Transaction Log Restoration

Question

Post reply

Transaction Log Restoration

Wecks

Right there with Babe

Points: 722
More actions
October 4, 2019 at 1:09 pm

#3686020

I have a full database and someone has set transaction logs to back every five mins. I need to restore to a point yesterday and there are hundreds.

Is there a script that I could simply run and apply each of the correct Tlogs in the correct order?

I know you can usually in SSMS highlight a lot however this doesn't appear to be working for me and it will only allow me to do 64 at a time anyway.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Michael L John One Orange Chip Points: 26551 More actions · Answer 1

There is a search feature on this site, and by typing in "Restore Database", and then filtering for "scripts", returned this article:

https://qa.sqlservercentral.com/scripts/build_restore_script

There are probably more in the list, this was the first one.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 2

Doing this from memory, so if it's not quite correct then feel free to tweak it.

SELECT 'RESTORE LOG <MyDB> FROM DISK = ''' + f.physical_device_name + ''' WITH NORECOVERY;'
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id -- or is it backup_set_id?
WHERE b.database_name = '<MyDB>'
AND b.backup_start_date < DATEADD(mi,'<DateandTimeoftheRestorePoint>',5) -- because your log backups are every 5 mins
AND b.backup_start_date > '<EndTimeoftheFullBackup>'
ORDER BY b.backup_start_date;

John

Wecks Right there with Babe Points: 722 More actions · Answer 3

I fear you believe my question to be simplistic in nature hence the response. I have reviewed posts many in fact and the script you mention is not really what I am after hence why I have gone for a more direct approach. I hope to utilise some useful scripts into my one I am working and looking for ideas.

When restoring multiple tlogs, scripts have looked as do not really work, some of the tlogs are applied in the correct order however they do not account for the tlogs being not being in the correct order and as such the LSNs do not follow each other and so the scripts fail to restore.

I guess I am really looking for a script that will restore the correct backup file based off of the next LSN in line and then keep going until the files have been restored.

Thank you John M for your post I will take a look at what you sent.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 4

This should get you started

Declare @database sysname = '{your database}';

Declare @backup_set_id_start int 
      , @backup_last_lsn numeric(25,0);

 --==== Get Backup Set for this database
 Select @backup_set_id_start = max(backup_set_id)
      , @backup_last_lsn = max(last_lsn)
   From msdb.dbo.backupset
  Where Type = 'D'
    And database_name = @database; 

 Select b.backup_set_id
      , b.last_lsn
      , mf.physical_device_name
   From msdb.dbo.backupset b
  Inner Join msdb.dbo.backupmediafamily mf On mf.media_set_id = b.media_set_id
  Where b.type = 'L'
    And b.database_name = @database
    And b.last_lsn > @backup_last_lsn
  Order By
        b.backup_set_id;

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs