January 18, 2017 at 1:11 pm
I have a database that i want to restore to the development environment.
It's in simple mode, and i have an array of both FULL backups and DIFF backups over the course of the week that occurred on production.
I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
My question is can i "toggle" the database so I can restore the DIFF, or do i have to start over?
January 18, 2017 at 1:30 pm
Start over, unfortunately.
I've actually got into the habit of doing all restores WITH NORECOVERY, then running RESTORE DATABASE dbname WITH RECOVERY when I'm done. The last step is usually near-instantaneous.
Edit: also, welcome to SSC, great username!
January 18, 2017 at 1:51 pm
bobbytables - Wednesday, January 18, 2017 1:11 PMI have a database that i want to restore to the development environment.
It's in simple mode, and i have an array of both FULL backups and DIFF backups over the course of the week that occurred on production.I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
My question is can i "toggle" the database so I can restore the DIFF, or do i have to start over?
Gazareth is correct. Good question!
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
January 19, 2017 at 12:12 am
Restoring a differential backup is not like restoring a transaction log backup. If what you want is to simply restore an entire backupset all you need to do is copy the backupset to the backup folder of the target system and restore it in it's entirety.
The main difference here is that there is no possible means to restore to a specific Point-in-time (your database is running in SIMPLE Recovery Mode and Differential Backups do not allow Point-in-time) so it is very much an all or nothing task. Because of this, restoring the database with NORECOVERY doesn't actually achieve anything....
January 19, 2017 at 2:34 am
kevaburg - Thursday, January 19, 2017 12:12 AMBecause of this, restoring the database with NORECOVERY doesn't actually achieve anything....
???
If you want to restore a full backup, and then restore a differential on top, the full backup must be restored WITH NORECOVERY.
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
January 19, 2017 at 3:04 am
I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
He wanted to restore the database from the latest full and appropriate differential backup. In this case all he needs to do is select the appropriate backupsets in the SSMS and restore. There isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
January 19, 2017 at 3:23 am
kevaburg - Thursday, January 19, 2017 3:04 AMMy answer was based on this statement from the OP....I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
He wanted to restore the database from the latest full and appropriate differential backup. In this case all he needs to do is select the appropriate backupsets in the SSMS and restore. There isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
If you're using the GUI, yes. I prefer to use the RESTORE statement explicitly. That way, I know exactly what I'm getting, and I can more easily tweak the process and start again if anything goes wrong.
John
January 19, 2017 at 3:51 am
kevaburg - Thursday, January 19, 2017 3:04 AMThere isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
Yes there is. The fact that the GUI does the two steps for you as if it were one doesn't change that. To restore a full and then apply the diff, the full has to be restored WITH NORECOVERY.
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
January 19, 2017 at 4:32 am
John Mitchell-245523 - Thursday, January 19, 2017 3:23 AMkevaburg - Thursday, January 19, 2017 3:04 AMMy answer was based on this statement from the OP....I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
He wanted to restore the database from the latest full and appropriate differential backup. In this case all he needs to do is select the appropriate backupsets in the SSMS and restore. There isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
If you're using the GUI, yes. I prefer to use the RESTORE statement explicitly. That way, I know exactly what I'm getting, and I can more easily tweak the process and start again if anything goes wrong.
John
I think we misunderstand each other a little.....I was referring to what the OP has to do rather than than what happens in the background.The GUI is nothing more than the lazy way of performing the actions we would normally have to type out in TSQL in full......
January 19, 2017 at 4:36 am
GilaMonster - Thursday, January 19, 2017 3:51 AMkevaburg - Thursday, January 19, 2017 3:04 AMThere isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.Yes there is. The fact that the GUI does the two steps for you as if it were one doesn't change that. To restore a full and then apply the diff, the full has to be restored WITH NORECOVERY.
I agree in full but I wasn't referring to what happens in the background behind the GUI rather than what the OP himself actually has to do.
Actually, this is one of the reasons I don't much like GUIs.....it partially removes the need for the administrator to understand what is really happening behind the scenes and results in lazy administration.
January 19, 2017 at 5:10 am
kevaburg - Thursday, January 19, 2017 4:32 AMJohn Mitchell-245523 - Thursday, January 19, 2017 3:23 AMkevaburg - Thursday, January 19, 2017 3:04 AMMy answer was based on this statement from the OP....I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
He wanted to restore the database from the latest full and appropriate differential backup. In this case all he needs to do is select the appropriate backupsets in the SSMS and restore. There isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
If you're using the GUI, yes. I prefer to use the RESTORE statement explicitly. That way, I know exactly what I'm getting, and I can more easily tweak the process and start again if anything goes wrong.
John
I think we misunderstand each other a little.....I was referring to what the OP has to do rather than than what happens in the background.The GUI is nothing more than the lazy way of performing the actions we would normally have to type out in TSQL in full......
OK, understood, although he doesn't actually say whether he's using the GUI or not.
John
January 19, 2017 at 5:13 am
John Mitchell-245523 - Thursday, January 19, 2017 5:10 AMkevaburg - Thursday, January 19, 2017 4:32 AMJohn Mitchell-245523 - Thursday, January 19, 2017 3:23 AMkevaburg - Thursday, January 19, 2017 3:04 AMMy answer was based on this statement from the OP....I restored a FULL database backup, but i forgot to set the WITH NO RECOVERY so that i can restore the latest differential as well.
He wanted to restore the database from the latest full and appropriate differential backup. In this case all he needs to do is select the appropriate backupsets in the SSMS and restore. There isn't an inherrent requirement to restore the full backup in NORECOVERY and then apply the differential. He knows exactly which differential needs to be applied so he doesn't need the step in between.
If you're using the GUI, yes. I prefer to use the RESTORE statement explicitly. That way, I know exactly what I'm getting, and I can more easily tweak the process and start again if anything goes wrong.
John
I think we misunderstand each other a little.....I was referring to what the OP has to do rather than than what happens in the background.The GUI is nothing more than the lazy way of performing the actions we would normally have to type out in TSQL in full......
OK, understood, although he doesn't actually say whether he's using the GUI or not.
John
Oh.....the person that can read is clearly at an advantage! 🙂
I can take that one on the chin....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply