Point in time restore Not from latest .BAK file

  • Lets say I need to restore a db to from BACKUP A to TRANSLOG BACKUP Z.

    I have a script to restore and include the .trn files up to Backup Z. This works fine and can be done via the GUI.

    Lets take this scenario as an example. This is my issue.

    FULL Backup A

    Trans Backup a

    Trans Backup b

    Trans Backup c

    Trans Backup d

    FULL Backup B _Corruption Found

    Trans Backup e

    Trans Backup f

    Trans Backup g

    Trans Backup h

    FULL Backup C – Corruption Found

    Trans Backup i

    Trans Backup j

    Trans Backup k

    Trans Backup Z

    I need to restore up to ‘TRANS BACKUP Z’, However my script now breaks at the time that Full Backup B was created. This breaks my backup chain. How do I get around this and get it to restore FULL BACKUP A – TRANS BACKUP Z.

    The script I am using does have all the relevant TRN files listed to be loaded and are in the correct order. Why does the backup break the chain?

    Not a live issue but preparing for unfixable db corruption. To eliminate a lengthy downtime.

  • Can I only restore up to TRANS BACKUP D.

    This will mean data loss.

  • I believe (and thought I'd asked a similar question) that you can leave out the corrupted full backups, and simply restore from Full A, then all the TLog backups from A to Z.

    If I'm incorrect, maybe GilaMonster will hop in and smack me around, then provide the correct answer.

    😀

  • i second Talib123 here

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Yep thats what I'm trying to do. The post was mis-leading.

    Corrupt backups are not included in the Attempt to restore.

    BACKUP A

    TRANS A-Z

    Z restore with recovery.

    Backup B & C just to llustrate that further FULL backups have occured.

  • Full backups and the log backups are disconnected. You can go back to BackupA and then run all the transaction logs. Full backups and log backups are not linked like differentials. Differentials are always connected to the last full backup unless that backup was taken using copy_only. Log backups are different.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (9/25/2013)


    Log backups are different.

    But how does the LSN get matched ? i mean their sequence because that will be required to ordered restore

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (9/25/2013)


    Grant Fritchey (9/25/2013)


    Log backups are different.

    But how does the LSN get matched ? i mean their sequence because that will be required to ordered restore

    Take a look at the backup header of the full backup. It contains an LSN. So when you start restoring logs, SQL Server is smart enough to figure it out.

    You do have to have a complete chain of log backups. No getting around that. But they are not married to a particular full backup. Not at all.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (9/25/2013)


    Full backups and the log backups are disconnected. You can go back to BackupA and then run all the transaction logs. Full backups and log backups are not linked like differentials. Differentials are always connected to the last full backup unless that backup was taken using copy_only. Log backups are different.

    This is what I tried to do, but my restore fails at the point where the FULL BACKUP B occured. The very next TRN file fails. Ok as long as the theory is correct. I will bang on with it and figure it out.

  • Talib123 (9/25/2013)


    Grant Fritchey (9/25/2013)


    Full backups and the log backups are disconnected. You can go back to BackupA and then run all the transaction logs. Full backups and log backups are not linked like differentials. Differentials are always connected to the last full backup unless that backup was taken using copy_only. Log backups are different.

    This is what I tried to do, but my restore fails at the point where the FULL BACKUP B occured. The very next TRN file fails. Ok as long as the theory is correct. I will bang on with it and figure it out.

    Speaking of LSN's, check the numbers on the tran logs & full backups.

    Oh, wait, do you change the recovery model before the full backup? If so, that could be the issue. If you change the recovery model from simple to full, then the very next full backup and log backup are connected. After that, it's as I said. But that very, very first one it isn't.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • No it has always been in Full recovery mode.

    I'll try it again. As long as I know it should work then I'll bang on with it.

  • Thanks all cracked it.

    Started from scratch.

    1 Backed UP db.

    2 Set off T-log backups

    3 Captured 3/4 Tlogs.

    4 Ran my script.

    5 Created another Backup.

    6 Ran my script again.

    7 added missing TRNs to the output in step 4.

    8 Ran full output fine.

    Conclusion: It's my script when trying to go back to an older backup file. I need to amend as it is based on the latest .bak file + TRNs. I haven't quite tweaked it right.

    Does anyone have a script to do this? Or I'll have to look into this tomorrow maybe.

Viewing 12 posts - 1 through 11 (of 11 total)

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