none
How to tell if a backup chain is broken RRS feed

  • Question

  • Is there a way to tell that a backup chain has been broken prior to issuing a backup? I am using a PowerShell script with SMO to backup my databases, and I wanted to add some conditional logic to Run a new Full backup if the chain is broken but a Diff or tlog backup was requested to run. 


    John M. Couch

    Wednesday, October 3, 2012 4:21 AM

Answers

  • Hi John,

    If you want to know about the broken LSN on primary database you can easily get it from this query

    All you need to do is replace the db name and it'll show you the relevant last LSN value, if this returns Null that means the chain is broken.

    SELECT db_name(database_id) AS 'database', last_log_backup_lsn FROM sys.database_recovery_status

    WHERE database_id = db_id('sarab_12')



    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by John Couch Wednesday, October 10, 2012 5:55 PM
    Tuesday, October 9, 2012 5:35 AM

All replies

  • Hi John,

    It is easy to tell if you are doing log-shipping: it is not restoring any more on the stand by computer.

    I don't think you can tell if broken until you start restoring them. Here is why: assume someone backed up the log manually to a different folder then deleted the file. Probably with Human Intelligence it can be figured out what happened, but not with a query.

    Related article:

    Script to retrieve SQL Server database backup history and no backups


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    Wednesday, October 3, 2012 4:59 AM
    Moderator
  • Check http://beyondrelational.com/modules/2/blogs/70/posts/10950/identifying-the-backup-type-of-backup-file.aspx

    If HeaderOnly fails, then the backup is broken, but the opposite is not always true.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, October 3, 2012 1:32 PM
    Moderator
  • To be honest, I think i am going to have to give up on it. What I was looking to do was have a FULL backup automatically kicked off if someone tried to kick a Log or DIFF and the backup chain was broken. since I can't guarantee, outside of the standard drop point for our disk level backups, that I could process every backup file to verify, there is really no way for me to do that.

    John M. Couch

    Tuesday, October 9, 2012 3:43 AM
  • Hi John,

    If you want to know about the broken LSN on primary database you can easily get it from this query

    All you need to do is replace the db name and it'll show you the relevant last LSN value, if this returns Null that means the chain is broken.

    SELECT db_name(database_id) AS 'database', last_log_backup_lsn FROM sys.database_recovery_status

    WHERE database_id = db_id('sarab_12')



    Sarabpreet Singh Anand

    Blog , Personal website

    This posting is provided , "AS IS" with no warranties, and confers no rights.

    Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    • Marked as answer by John Couch Wednesday, October 10, 2012 5:55 PM
    Tuesday, October 9, 2012 5:35 AM
  • Hello,

    Instead of processing backup file you can use below command to verify

    RESTORE VERIFYONLY FROM DISK = 'C:\Program Files\Backup\AdminQandQ.bak'

     

    ganeshk

    Tuesday, October 9, 2012 9:33 AM