July 18, 2013 at 12:07 pm
Hi,
We have full, differential and log backups into different folders as below:
1. Z:\Backups\db_name\full\DB_name_FULL_20130714_013225.bak -- Weekly once
2. Z:\Backups\db_name\diff\DB_Name_DIFF_20130717_190949.bak --Daily once
3. Z:\Backups\db_name\log\DB_Name_LOG_20130718_110116.trn --every 15 mins
Restore:
1) I'm able to restore full and diff with no difficulties
2) As log backups are running every 15 mins and there are hundreds of files to restore and it's taking hours to restore one-by one.
Is there any scripts which will give the auto generated scripts for at least log backups?
How you guys do these kind of restores in enterprise level organizations?
Thanks
July 18, 2013 at 1:13 pm
We use an automated process to continually restore 10 minute logs to a DR site for many databases. The process is really a stored procedure that runs from a control table. The "nextfiledatetime" is computed (based on the log interval in the table) and updated to column and used to construct the backup filename. The process will continue to run and restore logs until it catches up to the current time minus a safety window. It is also smart enough to skip over several "missed" logs or restart the log shipping chain from a backup if broken more than 24 hours. Running full bore (in "catch-up" mode) doesn't take long... half a days worth of logs can catch up in like maybe 20 minutes or so, depending on how much transactional activity took place. With 15 minute intervals it should not take hours.
Its really not hard to write a process to automate this activity. We also have our own procedures for creating the database and trans-log backups so we can control the file name specification.
The probability of survival is inversely proportional to the angle of arrival.
July 18, 2013 at 1:22 pm
Scripting this out from msdb is the way to go. All the information you need to generate a script for this is stored in msdb already. The link below has a great starter script that will generate all the Full, Diff, and Log scripts needed to restore up to the last Log taken.
http://sqlserveradvisor.blogspot.com/2009/02/generate-restore-scripts-from-backup.html
Hope that helps!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply