June 17, 2009 at 8:18 pm
Hi,
We restored our full backup WITH NORECOVERY, it was successful. then we restored our first differential backup WITH RECOVERY, it was also successful. But on our attemp to restore our second differential backup WITH RECOVERY, we received this kind of error:
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What could probably be the problem and how can we resolve it?
Thanks
June 17, 2009 at 9:10 pm
isa_sey (6/17/2009)
Hi,We restored our full backup WITH NORECOVERY, it was successful. then we restored our first differential backup WITH RECOVERY, it was also successful. But on our attemp to restore our second differential backup WITH RECOVERY, we received this kind of error:
Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What could probably be the problem and how can we resolve it?
Thanks
First, by using the WITH RECOVERY clause on the restore of the differential backup, you were telling SQL Server that this was the last file you were restoring.
Also, When restoring a full and differential backup files, you only need to restore the LAST differential backup taken. If you have taken 4 differential backups after your last full backup, you only need to restore the last (4th) differential file. You do not need to restore the first three. A differential backup backs up ALL changes since the last full backup.
If you also have any transaction log backups to restore, you only need to use those taken after the last differential backup. Also, if you do, do not restore the differential using the WITH RECOVERY clause, use the WITH NORECOVERY clause. Only the last file being restored needs to have the WITH RECOVERY clause (which, iirc, is optional).
In this case, you need to restart your restore with you full backup.
June 17, 2009 at 9:25 pm
Thanks Lynn, what we are trying to do is we execute differential backup every night, then we restore it every morning so that our mirror database is always updated. that's why we restore our latest diff backups everyday.
what do you think is the best approach for this?
thank you very much!
June 17, 2009 at 9:32 pm
NEI. Not Enough Information. What exactly are you trying to accomplish? Is the mirror database used for any purpose during the day?
June 17, 2009 at 9:41 pm
Yes, the mirror database is used for reporting. the backup is from our production database. We want to restore to it everyday to keep it updated. When we restored the full backup with norecovery, it is successful, then when we restored the first diff backup with recovery, it is also successful, then when we tried to restore the second diff backup with recovery, it already failed.
Based on your explanation, we should have used with norecovery when we restored our first diff backup because we needed to restore the rest of the diff backups. is it possible that we restore every diff backup? is it the right strategy?
June 17, 2009 at 9:47 pm
Once you do a RESTORE ... WITH RECOVERY, no more backup files may be applied. If you are restoring this database daily for reporting using your current process, you would restore the most current full backup with NO RECOVERY followed by the MOST RECENT differential backup file with RECOVERY. You only need to restore the MOST CURRENT differential backup file, NOT all of them.
Remember, EACH differential file contains ALL updates to the database since the last full backup. If you need to restore the fourth differential backup, you don't need the first three.
June 17, 2009 at 9:49 pm
What is the recovery model of your PRODUCTION database? If it is using the Full Recovery model, you might want to look at using log shipping to keep you mirrored/reporting database current.
June 17, 2009 at 9:54 pm
You may also want to read Books Online (BOL, the SQL Server Help System), as you may be able to use the STANDBY clause instead of the RECOVERY clause with the restores. This will allow you to use the reporting database in a read only fashion for reporting and still be able to apply subsequent backups each day.
June 17, 2009 at 9:55 pm
Alright, so made a wrong strategy on mirroring. Instead of restoring differential backups, can we restore logs instead? still for the purpose of mirroring.
June 17, 2009 at 10:05 pm
atlast after so many exchanges you got the answer .
Yes , you need to keep on applying the logs for the updates to that the snapshot on the mirrored database captures the changes based on principal of copy on write .
So lets say you have Log1 , log 2 and log 3 .
once you apply log 1 with no recovery on to the mirror, the changes before log1 will move to the snapshot and you can use it for reporting .
Once you apply log3 changes made by log 2 will first move to the snapshot and it will be ready for reporting .
So there are 2 corrections :
1) Yes you can apply the differential backups instead of applying the log backups as 'n' number of log backups are not needed when you have one differential backup (take it just before you want to restore it on the mirror) ...
2) restore it with no recovery as you will break mirroring if you restore anything with recovery
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 17, 2009 at 11:33 pm
we use simple recovery model for our production database
June 18, 2009 at 12:11 am
Since you are running your production database using the simple recovery model you don't have the option of taking transaction log backups.
Try restoring your database as follows and see if it allows you to run select queries (reports) against the database.
RESTORE DATABASE ... WITH STANDBY (This first one will by your full backup)
test reporting
RESTORE DATABASE ... WITH STANDBY (Your first differential backup)
test reporting
RESTORE DATABASE ... WITH STANDBY (Your second differential backup)
... repeat until you have restored all differential backups.
Please note that I am leaving details out of the process. You may also need to specify undo files. You need to read more in BOL about RESTORE DATABASE to be sure of the complete syntax required here.
June 18, 2009 at 12:35 am
Why to apply so many differential backups when one would suffice 🙂
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 18, 2009 at 12:38 am
hi_abhay78 (6/18/2009)
Why to apply so many differential backups when one would suffice 🙂
He's restoring to the reporting server everyday morning with diff backup...
log shipping would have been a better option with restore interval kept to a day.
edit : added comments.
June 18, 2009 at 2:28 am
Thank you so much! We'll try you're restore suggestion later, will update you after it.
Thanks 😀
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply