T-log backup file finder

ACDBA 416 Reputation points
2022-03-19T17:54:39.453+00:00

Hi All,

I have a requirement to do a point in time restore till 10.00 AM. Do we have a SQL query to find out the correct t-log backup file which is to be used to get the transaction till 10.00AM.

I still want the next file even though i have a backup that completes by 9.59 AM.

Regards,
ACDBA

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,896 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2022-03-19T19:20:49.457+00:00

    OK, if you need to schedule it, you something which is fully automated. As I said, I only need to do this ad-hoc.

    Here is a script, but it will probably need some fine-tuning. For one thing, where I sit at home, I don't have a set up of backups to test on. But it should get you started. Please consult the documentation for the tables backupset and backupfile as well as the documentation for the RESTORE statement for details.

       DECLARE @fullbackup numeric(25,0),  
               @filename   nvarchar(260),  
               @cur        CURSOR,  
               @stoptime   datetime2(3) = dateadd(HOUR, 10,   
                                            convert(datetime2, convert(date, sysdatetime())))  
         
       \-- Restore full backup.  
       SELECT TOP (1) @fullbackup = s.last_lsn,  
                      @filename = f.physical_name  
       FROM   msdb.dbo.backupset s  
       JOIN   msdb.dbo.backupfile f ON s.backup_set_id = f.backup_set_id  
       WHERE  s.database_name = 'yourdb'  
         AND  s.backup_finish_date < @stoptime  
         AND  s.type = 'D'  
       ORDER BY s.backup_finish_date DESC  
         
       RESTORE DATABASE yourdb FROM DISK = @filename WITH NORECOVERY  
         
       \-- Get all intermediate log backups.  
       SET @cur = CURSOR STATIC FOR  
          SELECT f.physical_name  
          FROM   msdb.dbo.backupset s  
          JOIN   msdb.dbo.backupfile f ON s.backup_set_id = f.backup_set_id  
          WHERE  s.database_name = 'yourdb'  
            AND  s.type     = 'L'  
            AND  s.last_lsn > @fullbackup  
            AND  s.backup_start_date < @stoptime  
          ORDER  BY s.last_lsn  
         
       OPEN @cur  
         
       WHILE 1 = 1  
       BEGIN  
          FETCH @cur INTO @filename  
          IF @@fetch_status <> 0  
             BREAK  
         
          RESTORE LOG yourdn FROM @filename WITH NORECOVERY  
       END  
         
       \-- Find the last log  
       SELECT TOP (1) @filename = f.physical_name  
       FROM   msdb.dbo.backupset s  
       JOIN   msdb.dbo.backupfile f ON s.backup_set_id = f.backup_set_id  
       WHERE  s.database_name = 'yourdb'  
         AND  s.type     = 'L'  
         AND  s.backup_start_date > @stoptime  
       ORDER BY s.backup_start_date ASC  
         
       RESTORE LOG yourdb FROM @filename WITH RECOVERY, STOPAT = @stoptime  
    

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2022-03-19T18:13:44.193+00:00

    I have some queries for this, but they are on a work laptop, and right now I'm enjoying the weekend. :-)

    But you have the backups in msdb.dbo.backupset, and the actual files in msdb.dbo.backupfil. Join over backup_set_id.

    When I do this I typically pull out an approximate set of statements, and make it up as I go along. But I only do this very rarely - when I need to diagnose a problem that has occurred in our system. Never for disaster recovery.