Post reply

Previous backups invalid

  • October 16, 2014 at 12:00 am

    #300233

    Hi,

    I read somewhere that, when we change the recovery model of a database from FULL to SIMPLE or SIMPLE to FULL to BULK. All previous backups become invalid.

    Could anyone help me to understand why and is there a sys table where I can find if my previous backup are invalid after changing the recovery model.

    Thanks

    Kummu

  • October 16, 2014 at 3:50 am

    #1752779

    Changing the recovery model from FULL to SIMPLE would break the LOG-chain. This implies a point-in-time restore after this moment is not possible anymore. All previous FULL and DIFF backups can still be used without any issue. Also all LOG backups uptill this moment can still be used to restore the database to a point-in-time uptill the moment of the change.

    After you change the recovery model back to FULL you need to create a new FULL or DIFF backup. After that backup you can create new LOG backups wich will form a new chain. You can use these to restore the database to a point-in-time after the moment of the FULL/DIFF backup.

    It will not be possible to restore the database to a point-in-time during the period when the recovery model of that database was SIMPLE.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 16, 2014 at 3:54 am

    #1752780

    Kummu (10/16/2014)

    I read somewhere that, when we change the recovery model of a database from FULL to SIMPLE or SIMPLE to FULL to BULK. All previous backups become invalid.

    Nope, completely untrue. Full and differential backups are completely unaffected by changes in the recovery model.

    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

  • October 16, 2014 at 9:49 am

    #1752908

    Thanks for your reply.

    I'm using SQLSafe for backups, when recovery model is changed from simple to full or full to simple and then try to run a diff/log my backup job fails with "No current database backup, perform full backup and retry".

    Does anyone here use SQLSafe and has same issue?

    Any idea how and why is this behaving like this?

    Any suggestion on how to fix this issue?

    Thanks

    Kummu

  • October 16, 2014 at 10:31 am

    #1752919

    Kummu (10/16/2014)

    Thanks for your reply.

    I'm using SQLSafe for backups, when recovery model is changed from simple to full or full to simple and then try to run a diff/log my backup job fails with "No current database backup, perform full backup and retry".

    Does anyone here use SQLSafe and has same issue?

    Any idea how and why is this behaving like this?

    Any suggestion on how to fix this issue?

    Thanks

    Kummu

    The error message is telling just what you need to do, perform a full backup and then retry the diff/log backup. Remember, you can't run a log backup when a database is using the SIMPLE recovery model.

  • October 16, 2014 at 2:09 pm

    #1752962

    Kummu (10/16/2014)

    Thanks for your reply.

    I'm using SQLSafe for backups, when recovery model is changed from simple to full or full to simple and then try to run a diff/log my backup job fails with "No current database backup, perform full backup and retry".

    Does anyone here use SQLSafe and has same issue?

    Any idea how and why is this behaving like this?

    Any suggestion on how to fix this issue?

    Thanks

    Kummu

    I'm not familiar with SQLSafe, but the message "No current database backup, perform full backup and retry" indicates a LOG backup is taken. So when you perform

    a diff/log [/ quote] you are actually performing a LOG backup and not a differential backup.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 16, 2014 at 2:19 pm

    #1752969

    You can get the same message with a differential. I've seen it before when a full was made one way (native backup) and the diff was run with something else (like backupexec)

    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 7 posts - 1 through 7 (of 7 total)

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