October 4, 2019 at 1:09 pm
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.
October 4, 2019 at 1:31 pm
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/
October 4, 2019 at 1:39 pm
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
October 4, 2019 at 8:41 pm
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.
October 5, 2019 at 2:38 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply