August 22, 2017 at 6:13 pm
I have a question to DBAs for restoring database using SQL server native tools from production to test or development environment.
Do you often copy the back up file to test environment to restore it, or you use network path - UNC path to restore database?
Which one is more recommended ? if size is below 10 GB or size is above 50 GB?
Thanks!
August 22, 2017 at 6:23 pm
I prefer to copy the backup to the "test" or "dev" server to perform the restore. These days that is really more about preference than anything else.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 22, 2017 at 9:28 pm
I do the same as Jason - copy the file to the server you want to restore to, then fire the restore.
August 22, 2017 at 11:15 pm
Depends on what else is using the thing behind the UNC path, how reliable the connection to it is and how fast the server doing the restore happens to be. If it's all stable and fast I typically do not bother copying the file locally since that's potentially two extra steps (including removal of the local copy of the backup file) in my code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 23, 2017 at 8:25 am
Copy, then restore. Generally simplifies troubleshooting if the operation goes wrong. Some clients still have networks that just aren't man enough, not unknown for a tiny 20gb copy to fail with some obscure network-level error whining about CRC or whatever. Depending on the quality of yours, as well as how non-Blob your data is, you may want to compress first, if you aren't already.
August 23, 2017 at 12:38 pm
Depends on where the backups are stored really. If you're backing up to your production server, copy then restore, always (and stop backing up directly to the production server). If we're talking about a shared storage resource away from the production server, generally I just use the UNC to restore directly. That does depend on the network setup, speed, and whether or not we can witness any issues in or around the production server because of this (blocking a log backup or something). There's usually not an issue, but situationally, I have seen them. I just like to move the bits around as little as possible. Copying then restoring means I'm moving the bits twice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 24, 2017 at 8:04 am
Grant Fritchey - Wednesday, August 23, 2017 12:38 PMDepends on where the backups are stored really. If you're backing up to your production server, copy then restore, always (and stop backing up directly to the production server). If we're talking about a shared storage resource away from the production server, generally I just use the UNC to restore directly. That does depend on the network setup, speed, and whether or not we can witness any issues in or around the production server because of this (blocking a log backup or something). There's usually not an issue, but situationally, I have seen them. I just like to move the bits around as little as possible. Copying then restoring means I'm moving the bits twice.
Well, maybe stop backing up directly to the production server. I like to have a backup local to the production sever as well as in a remote "backup" share. Multiple levels of protection that way in the event something goes really south.😉 That is easy enough to do with mirrored backups or copying to the other location.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2017 at 9:03 am
SQLRNNR - Thursday, August 24, 2017 8:04 AMGrant Fritchey - Wednesday, August 23, 2017 12:38 PMDepends on where the backups are stored really. If you're backing up to your production server, copy then restore, always (and stop backing up directly to the production server). If we're talking about a shared storage resource away from the production server, generally I just use the UNC to restore directly. That does depend on the network setup, speed, and whether or not we can witness any issues in or around the production server because of this (blocking a log backup or something). There's usually not an issue, but situationally, I have seen them. I just like to move the bits around as little as possible. Copying then restoring means I'm moving the bits twice.Well, maybe stop backing up directly to the production server. I like to have a backup local to the production sever as well as in a remote "backup" share. Multiple levels of protection that way in the event something goes really south.😉 That is easy enough to do with mirrored backups or copying to the other location.
Yeah, let's make it "only" to the production server. Although, I'm pretty sure there are plenty of prod servers that don't really have "local" disks as such except for the C:\ drive.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply