T-log backup file finder

Question

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

Answer 1

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  

Answer 2

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.