restoring a differntial backup

  • I have been unable to restore a differntial backup .

    I restored the fullbackup but when I go to do the differential it says I need to "set the norecovery option for all steps except the last one"

    Help please.

    -K

  • If doing the FULL restore thru QA add WITH NORECOVERY to it then do the Diff. If thru EM then on the second tab of the restore dialog you will find three options near the bottom one of which states something to the effect additional restores not to recover.

  • Something to remember....

    WITH RECOVERY (which is the default) means this is all that I have to restore. WITH NO RECOVERY means I have more files to restore.

    -SQLBill

  • I must be missing something. I am using EM. I can restore the full backup and differential but I cannot restore the backup , close out and then restore the diff backup regardless of which options I try. ???

  • Ok, on the Options table of the Restore database dialog then is an option the exact wording in 2000 and I believe 7 is

    "Leave database nonoperational but able to restore additional transaction logs"

    Even thou it doesn't mention Diffs that is the option you want to set. When you do this it will perform the NORECOVERY option on the DB with the full back. Then you restore the differential backup and make sure the option is set to the one that reads

    "Leave database operational. No additional transacion logs can be restored."

    which will do that one with RECOVERY bringing the DB back up and live for transactions.

  • quote:


    I must be missing something. I am using EM. I can restore the full backup and differential but I cannot restore the backup , close out and then restore the diff backup regardless of which options I try. ???


    I believe you have to do all restores one right after the other.

    -SQLBill

  • Get out of EM.!!!!

    From BOL

    B. Restore a full database and a differential backup

    This example restores a full database backup followed by a differential backup restore. In addition, this example shows restoring the second backup set on the media. The differential backup was appended to the backup device that contains the full database backup.

    RESTORE DATABASE MyNwind

    FROM MyNwind_1

    WITH NORECOVERY

    RESTORE DATABASE MyNwind

    FROM MyNwind_1

    WITH FILE = 2

    John Zacharkan


    John Zacharkan

  • I think even you can try for overwrite option to restore differntial backup

  • Please note. 7 year old thread

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply