How to determine backup files needed for PIT recovery?

  • richj-826679

    Mr or Mrs. 500

    Points: 575

    Hi,

    Due to vendor app issues, I've been needing to restore a small (200MB) database several times to a point-in-time.  The DB is in full-recovery mode.  There are no differentials, no log shipping, but the vendor will run copy-only backups occasionally.

    Prior to restoring, I take a full backup in case something goes wrong with the restore, I can at least get back to where I was (admittedly, I'm rethinking this action).  Post-restore, I take another full backup to initialize the log for transaction log backups.

    The first point-in-time restore always works.  If I need to do another, SSMS (17.9 or 18.2) refuses, claiming the LSN chain is broken.  It's not, as I manually build the restore script using the last full and the subsequent logs.  So I'm looking to build a query that will give me the files needed for restoring to a point-in-time.

    I join msdb.dbo.backupset for the backups to msdb.dbo.backupmediafamily for the filenames on media_set_id.  But how does one know where to start and stop?  I've tried using the database_backup_lsn to gather other log backups with the same, as well as the full that has the same checkpoint_lsn.  While that works most of the time, I can get an extra log in there if the automated backups kick in before I get my post-restore full done.

    I do have my incorrect SQL I could post, but someone has to already have done this, right?

    TIA,

    Rich

     

  • Ronzo

    Hall of Fame

    Points: 3741

    Is there a reason you are not using SQL Server Management Studio to do your database restore? The GUI provides an excellent interface for point in time recovery, automatically listing and including the appropriate full and log backup files. It even has a nice graphic display of the timeline and allows specification of the date and time you want to recover to.


    Have Fun!
    Ronzo

  • richj-826679

    Mr or Mrs. 500

    Points: 575

    Hi,

    As I mentioned, SSMS incorrectly claims the LSN chain is broken, so I'm needing to bypass it.

    Thanks,

    Rich

Viewing 3 posts - 1 through 3 (of 3 total)

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