December 17, 2017 at 6:21 pm
Let me explain.
I have a big database (250 gigs) that I need to restore on another machine. Copying and restoring this DB is a long process. To help with time frame here what I want to do.
Full backup is taken on Tuesday
Wenesday I will to copy the backup file to the new machine and restore the full backup in the new machine (it will take several hours)
Friday eveving I will make a differential backup, copy the file to the new machine and restore the differential backup only (which should take a few minutes)
can I do this?
I know I could restore at the same time the full and differential backup files but it will take hours. My goal would to restore the full backup at first (no one will use the DB) then restore the diff backup later.
December 18, 2017 at 12:45 am
Yup, no problem, providing you restore the full backup WITH NORECOVERY, and that there are no full backups taken between Tuesday and Friday evening.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2017 at 5:52 am
Great thanks for your answer.
"With No recovery" that's what I was missing. So I guess when I restore the diff backup I'll do it "With recovery" ?
December 18, 2017 at 5:53 am
dubem1-878067 - Monday, December 18, 2017 5:52 AMGreat thanks for your answer.
"With No recovery" that's what I was missing. So I guess when I restore the diff backup I'll do it "With recovery" ?
Yes, correct, unless of course you want to restore further files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 18, 2017 at 6:57 pm
I could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2017 at 4:05 am
Jeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.
depends on the stability of your network
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 19, 2017 at 6:25 am
Perry Whittle - Tuesday, December 19, 2017 4:05 AMJeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.depends on the stability of your network
Interesting. Are you saying that you've actually seen a "direct restore" take more time than it did to do an OS level "COPY" from the same source to local and then doing a restore from that local copy? I ask because I've never seen such a thing happen and, especially because I know whom I'm talking with on the subject, am seriously curious about the circumstances that could lead to such a thing, if you have the time.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2017 at 5:08 am
Jeff Moden - Tuesday, December 19, 2017 6:25 AMPerry Whittle - Tuesday, December 19, 2017 4:05 AMJeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.depends on the stability of your network
Interesting. Are you saying that you've actually seen a "direct restore" take more time than it did to do an OS level "COPY" from the same source to local and then doing a restore from that local copy? I ask because I've never seen such a thing happen and, especially because I know whom I'm talking with on the subject, am seriously curious about the circumstances that could lead to such a thing, if you have the time.
I've had network restores fail due to network issues and ended up having to copy the file locally then perform the restore
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 20, 2017 at 8:56 am
Perry Whittle - Wednesday, December 20, 2017 5:08 AMJeff Moden - Tuesday, December 19, 2017 6:25 AMPerry Whittle - Tuesday, December 19, 2017 4:05 AMJeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.depends on the stability of your network
Interesting. Are you saying that you've actually seen a "direct restore" take more time than it did to do an OS level "COPY" from the same source to local and then doing a restore from that local copy? I ask because I've never seen such a thing happen and, especially because I know whom I'm talking with on the subject, am seriously curious about the circumstances that could lead to such a thing, if you have the time.
I've had network restores fail due to network issues and ended up having to copy the file locally then perform the restore
Understood... would that not also affect a copy attempt?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2017 at 9:42 am
Jeff Moden - Wednesday, December 20, 2017 8:56 AMPerry Whittle - Wednesday, December 20, 2017 5:08 AMJeff Moden - Tuesday, December 19, 2017 6:25 AMPerry Whittle - Tuesday, December 19, 2017 4:05 AMJeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.depends on the stability of your network
Interesting. Are you saying that you've actually seen a "direct restore" take more time than it did to do an OS level "COPY" from the same source to local and then doing a restore from that local copy? I ask because I've never seen such a thing happen and, especially because I know whom I'm talking with on the subject, am seriously curious about the circumstances that could lead to such a thing, if you have the time.
I've had network restores fail due to network issues and ended up having to copy the file locally then perform the restore
Understood... would that not also affect a copy attempt?
I have had similar issues with restores and the copy to local worked where the restore didn't. For what ever reason the copy was not affected be poor networks as a restore over the same network.
December 20, 2017 at 9:57 am
dubem1-878067 - Monday, December 18, 2017 5:52 AMGreat thanks for your answer.
"With No recovery" that's what I was missing. So I guess when I restore the diff backup I'll do it "With recovery" ?
I've heard it recommended to get into the habit of doing all restores WITH NORECOVERY. Only when you're sure you're done should you do RESTORE DATABASE MyDB WITH RECOVERY. That way, you're less likely to accidentally recover your database and have to start your restore again from the beginning. (It also makes it easier to generate RESTORE statements programmatically, particularly useful if you have a large number of log backups to restore. Just generate all statements with a WITH NORECOVERY, and do a RESTORE.. WITH RECOVERY at the end.)
John
December 20, 2017 at 10:55 am
Jeff Moden - Wednesday, December 20, 2017 8:56 AMPerry Whittle - Wednesday, December 20, 2017 5:08 AMJeff Moden - Tuesday, December 19, 2017 6:25 AMPerry Whittle - Tuesday, December 19, 2017 4:05 AMJeff Moden - Monday, December 18, 2017 6:57 PMI could be wrong and I certainly could be missing something but I'm thinking that instead of copying the backup to the other machine, why not have the other machine restore directly from the backup? Seems like it would save about half the time that way.depends on the stability of your network
Interesting. Are you saying that you've actually seen a "direct restore" take more time than it did to do an OS level "COPY" from the same source to local and then doing a restore from that local copy? I ask because I've never seen such a thing happen and, especially because I know whom I'm talking with on the subject, am seriously curious about the circumstances that could lead to such a thing, if you have the time.
I've had network restores fail due to network issues and ended up having to copy the file locally then perform the restore
Understood... would that not also affect a copy attempt?
possibly, the network file copies are usually easier to deal especially with something like robocopy.
SQL server network restores don't seem to deal well with network blips, file copies are usually a little more tolerant.
The latest versions of Litespeed have disk and network resilience features for network restores and are a lot more reliable for a network restore than native
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply