locked
Help with restore full and differential RRS feed

  • Question

  • I really need help. No idea, why this error. I never got this in past.

     

    This differential backup cannot be restored because the database has not been restored to the correct earlier state

    I have full back from July 17th. and then every night differential.
    need to restore, July 23rd night. - Friday

    So I do restore with no recovery for full backup restore of 17th, and then with recovery of diff 23rd directly since this should have captured all since last full back of 17th, but i get above error.

    Any idea, what am I doing wrong.

     

     


    Frenk
    Tuesday, July 27, 2010 8:04 PM

Answers

  • Thanks for help.

    I have checked my SQL online backups and 17th is the last backup between 17th and 23rd as full backup. But I found out that Network guy is taking tape backups using some third party tool and that is nightly full backup. That tells me that he probably took full backup on 23rd or 22nd as well. That makes this one full back , not 17th one, right?

    I will do this test tomorrow with him at work but still I don't know how third party back has to do anything with SQL world and its chain of backup sequence.

     

    I tried both GUI and script and same message that clearly meant  that 17th is not last full back with respect to 23rd. but how???


    Frenk

    OK now everything makes sense.

    Backup is a backup, does not matter it has been taken using a third party tool or using SQL server itself.

     

    The way differential backup works, read this in books online http://msdn.microsoft.com/en-us/library/ms345448.aspx

    A differential backup is based on the most recent, previous full backup. This is known as the base of the differential. A differential backup includes only the data that has changed since the differential base.

    At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

    So find the latest backup from your network guy and use your latest differential...

    HTH,

    Wednesday, July 28, 2010 1:54 PM

All replies

  • Your understanding is right ....differential backup is cumulative (i.e. latest diff backup should include all changes since last full backup)

     

    a) Are you sure that July 17th backup is the last full backup...? I

    Diff backup corresponds to last full backup .. Also, full backup need to restore using norecovery and apply latest diff with recovery it looks like you are right...

     b) How are you doing using SSMS GUI or script, if you are using scripts to peform restore mind posting them here....

    Tuesday, July 27, 2010 8:40 PM
  • Thanks for help.

    I have checked my SQL online backups and 17th is the last backup between 17th and 23rd as full backup. But I found out that Network guy is taking tape backups using some third party tool and that is nightly full backup. That tells me that he probably took full backup on 23rd or 22nd as well. That makes this one full back , not 17th one, right?

    I will do this test tomorrow with him at work but still I don't know how third party back has to do anything with SQL world and its chain of backup sequence.

     

    I tried both GUI and script and same message that clearly meant  that 17th is not last full back with respect to 23rd. but how???


    Frenk
    Wednesday, July 28, 2010 1:54 AM
  • Thanks for help.

    I have checked my SQL online backups and 17th is the last backup between 17th and 23rd as full backup. But I found out that Network guy is taking tape backups using some third party tool and that is nightly full backup. That tells me that he probably took full backup on 23rd or 22nd as well. That makes this one full back , not 17th one, right?

    I will do this test tomorrow with him at work but still I don't know how third party back has to do anything with SQL world and its chain of backup sequence.

     

    I tried both GUI and script and same message that clearly meant  that 17th is not last full back with respect to 23rd. but how???


    Frenk

    OK now everything makes sense.

    Backup is a backup, does not matter it has been taken using a third party tool or using SQL server itself.

     

    The way differential backup works, read this in books online http://msdn.microsoft.com/en-us/library/ms345448.aspx

    A differential backup is based on the most recent, previous full backup. This is known as the base of the differential. A differential backup includes only the data that has changed since the differential base.

    At restore time, before you restore a differential backup, you must restore its base. Then, restore only the most recent differential backup to bring the database forward to the time when that differential backup was created. Typically, you would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

    So find the latest backup from your network guy and use your latest differential...

    HTH,

    Wednesday, July 28, 2010 1:54 PM