Problem Restoring 2nd and Succeeding Differential Backup

  • 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

  • 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.

  • 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!

  • NEI. Not Enough Information. What exactly are you trying to accomplish? Is the mirror database used for any purpose during the day?

  • 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?

  • 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.

  • 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.

  • 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.

  • Alright, so made a wrong strategy on mirroring. Instead of restoring differential backups, can we restore logs instead? still for the purpose of mirroring.

  • 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)

  • we use simple recovery model for our production database

  • 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.

  • Why to apply so many differential backups when one would suffice 🙂

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • 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.



    Pradeep Singh

  • 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