How to determine backup files needed for PIT recovery?

  • richj-826679

    SSChasing Mays

    Points: 608

    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

    SSChasing Mays

    Points: 608

    Hi,

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

    Thanks,

    Rich

  • Super Cat

    SSCertifiable

    Points: 7312

     

    --This will get the latest backup file and all the log files to the latest one.

    --It creates code so you can determine where the restore point ends.

     

    Use msdb

    go

    DECLARE @databaseName sysname

    DECLARE @backupStartDate datetime

    DECLARE @backup_set_id_start INT

    DECLARE @backup_set_id_end INT

    Declare @BackupSetID int

    Set @databasename ='    ' --set databasename here

    Set @BackupSetID = (select top 1 bs.backup_set_ID from backupmediafamily bmf

    inner join backupset bs on bs.media_set_id = bmf.media_set_id

     

    where type ='D'

    and database_name =@databasename

    and physical_device_name not like '%{%'

    order by backup_start_date desc

    )

     

     

     

    -----FIND BACKUP SET THE VALU BELOW.

    SELECT @backup_set_id_start = @BackupSetID--Change BACKUP_SET_ID

    FROM msdb.dbo.backupset b

    WHERE database_name = @databaseName AND type = 'D'

    SELECT @backup_set_id_end = MIN(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName AND type = 'D'

    AND backup_set_id > @backup_set_id_start

    IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

    SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''

    + mf.physical_device_name + ''' WITH NORECOVERY'

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_start

     

     

    -----------------------------------------------------------------------------------------------------------

     

     

    SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''

    + mf.physical_device_name + ''' WITH NORECOVERY'

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    ----------SET

    AND b.backup_set_id >= @BackupSetID AND b.backup_set_id < 999999999 ---Change BackUp_SET_ID

    AND b.type = 'L'

    UNION

    SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName+ ' WITH RECOVERY'

    ORDER BY backup_set_id

    --physical_device_name

  • Super Cat

    SSCertifiable

    Points: 7312

    -- Added in Line --and is_copy_only =0

     

    Use msdb

    go

    DECLARE @databaseName sysname

    DECLARE @backupStartDate datetime

    DECLARE @backup_set_id_start INT

    DECLARE @backup_set_id_end INT

    Declare @BackupSetID int

    Set @databasename =' ' --set databasename here#

    Set @BackupSetID = (select top 1 bs.backup_set_ID from backupmediafamily bmf

    inner join backupset bs on bs.media_set_id = bmf.media_set_id

     

    where type ='D'

    and is_copy_only =0 --added in

    and database_name =@databasename

    and physical_device_name not like '%{%'

    order by backup_start_date desc

    )

     

     

     

    -----FIND BACKUP SET THE VALU BELOW.

    SELECT @backup_set_id_start = @BackupSetID--Change BACKUP_SET_ID

    FROM msdb.dbo.backupset b

    WHERE database_name = @databaseName AND type = 'D'

    SELECT @backup_set_id_end = MIN(backup_set_id)

    FROM msdb.dbo.backupset

    WHERE database_name = @databaseName AND type = 'D'

    AND backup_set_id > @backup_set_id_start

    IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

    SELECT backup_set_id, 'RESTORE DATABASE ' + @databaseName + ' FROM DISK = '''

    + mf.physical_device_name + ''' WITH NORECOVERY'

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    AND b.backup_set_id = @backup_set_id_start

     

     

    -----------------------------------------------------------------------------------------------------------

     

     

    SELECT backup_set_id, 'RESTORE LOG ' + @databaseName + ' FROM DISK = '''

    + mf.physical_device_name + ''' WITH NORECOVERY'

    FROM msdb.dbo.backupset b,

    msdb.dbo.backupmediafamily mf

    WHERE b.media_set_id = mf.media_set_id

    AND b.database_name = @databaseName

    ----------SET

    AND b.backup_set_id >= @BackupSetID AND b.backup_set_id < 999999999 ---Change BackUp_SET_ID

    AND b.type = 'L'

    UNION

    SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @databaseName+ ' WITH RECOVERY'

    ORDER BY backup_set_id

    --physical_device_name

  • richj-826679

    SSChasing Mays

    Points: 608

    Hi Super Cat,

    Thanks for the script, but this only does a complete restore and not a point-in-time one.  SSMS can create this for me easily.  It's when there's multiple restores to the same point-in-time with full backups in between that SSMS gets confused.

    So, for better or for worse, here's my script.  It's based on empirical data and has the air of being easily broken.  I certainly hope that SQL Server recovery is not actually based on this method, because it seems very sloppy to me:

    DECLARE @DBToRestore nvarchar(128)
    DECLARE @PITDateTime varchar(128)

    -- @DBToRestore is the the database name requiring a restore.
    SET @DBToRestore = '' -- Enter DB Name here
    -- @PITDateTime in 'yyyy-mm-dd hh:mi:ss(24hr)' format
    SET @PITDateTime = '' -- e.g. '2019-08-12 15:48:00'

    BEGIN

    -- Start with the first backup taken after the PIT (i.e. the one that contains the proper database backup LSN).
    -- If this returns no rows, the result set will be NULL and the database is not recoverable to that PIT.
    WITH
    cte_lsn(database_backup_lsn, last_lsn, backup_start_rank)
    AS
    (
    SELECT TOP 1
    bs.database_backup_lsn,
    bs.last_lsn,
    rank() over (order by bs.backup_start_date) backup_start_rank
    FROM
    msdb.dbo.backupset bs
    WHERE
    bs.database_name = @DBToRestore
    AND convert(varchar,bs.backup_start_date,120) >= @PITDateTime
    ORDER BY 3
    )
    SELECT
    restore_cmd + end_point restore_cmd
    FROM
    (
    -- Grab the tlog backups.
    SELECT
    bs.backup_start_date,
    'RESTORE LOG RCPProd FROM DISK = ''' + bmf.physical_device_name + ''' WITH ' restore_cmd,
    -- Break the LSN chain when a previous restore is detected
    bs.checkpoint_lsn - LAG(bs.checkpoint_lsn,1,0) OVER (ORDER BY backup_start_date) incr_checkpoint_lsn,
    CASE WHEN LEAD(bs.checkpoint_lsn,1,0) OVER (ORDER BY backup_start_date) < bs.checkpoint_lsn
    THEN 'STOPAT = N''' + @PITDateTime +'''' ELSE 'NORECOVERY' END end_point
    FROM
    cte_lsn cl
    INNER JOIN msdb.dbo.backupset bs ON cl.database_backup_lsn = bs.database_backup_lsn
    INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
    WHERE
    bs.last_lsn <= cl.last_lsn
    AND bs.is_copy_only = 0
    UNION ALL
    -- Grab the full backup.
    SELECT
    bs.backup_start_date,
    'RESTORE DATABASE ' + @DBToRestore + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY',
    1, -- incr_checkpoint_lns placeholder
    '' -- end_point placeholder
    FROM
    cte_lsn cl
    INNER JOIN msdb.dbo.backupset bs ON cl.database_backup_lsn = bs.checkpoint_lsn
    INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
    WHERE
    bs.last_lsn <= cl.last_lsn
    AND bs.is_copy_only = 0
    ) subset
    WHERE incr_checkpoint_lsn > 0
    ORDER BY backup_start_date;

    END

    Hoping that someone can shed some light on the proper way to find this info...

    Thanks!

    Rich

  • Super Cat

    SSCertifiable

    Points: 7312

    HI,

    The script restores all logs after the FULL backup. How do you determine at which point in the last log file you need to restore to and what is the interval between Log backups.

     

     

  • sequelgarrett

    Right there with Babe

    Points: 783

    Generally: the log files first_lsn needs to be <= the full backups last_lsn and the log files last_lsn needs to be greater than the full backups last_lsn. When this condition is true that is the first restorable  log file. You can get this data from the metadata of the backup files. Use can use command RESTORE HEADER ONLY to get the metadata.

  • Lynn Pettis

    SSC Guru

    Points: 442144

    I am slightly confused.  Are you doing restores to the same point in time each time you do a restore?  Also, what are you attempting to achieve with these restores?

     

  • richj-826679

    SSChasing Mays

    Points: 608

    Super Cat wrote:

    The script restores all logs after the FULL backup. How do you determine at which point in the last log file you need to restore to and what is the interval between Log backups.

    The restore point is narrowed down by the application user, e.g. "I think I need to restore it back to around 3:45 PM".  From there, I use a tool to inspect the transaction logs taken around then to determine an exact time to restore.

    The log backups run every 15 minutes to meet our RPO for DR.

    Thanks,

    Rich

  • richj-826679

    SSChasing Mays

    Points: 608

    Lynn Pettis wrote:

    I am slightly confused.  Are you doing restores to the same point in time each time you do a restore?  Also, what are you attempting to achieve with these restores?

    In this case, yes, the restores were done to the same point-in-time.  The issue is that in between those restores, there was another full backup and subsequent transaction log backups, which is what I think confused SSMS into thinking the LSN chain was broken.

    Thanks,

    Rich

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

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