SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Faster Log Shipping Restores

By Richie Lee,

If you use a secondary readable log-shipped database for reporting, you will probably find that the restore time of the backups can fluctuate wildly. This is because of the way that the STANDBY restore mechanism in SQL Server works: any uncommitted transactions in the restore file are rolled back and stored in a transaction undo (tuf) file. Chances are you will notice the restore times are longest when the tuf file is largest. This really is no coincidence. If your log backup frequency is pretty short (think 5 - 15 minutes) and you have uncommitted transactions that span several log backups, you can clearly see why restore times on a readable secondary can take so long. Consider the fact that the undo uncommitted transactions and tuf file step are also redundant when you are about to restore another backup file.

The script provided has two parts. The first is a user defined table type that is used within the main part of the script. The second part is a stored procedure which is designed to replace the default restore agent job in log shipping.

The stored procedure provided will restore all log backups using the NORECOVERY mode, and then will switch to STANDBY mode for the very last restore file. This reduces the time that the database is unavailable to as short as possible as it removes the need to rollback uncommitted transactions and create a tuf file for every restore. I have commented liberally in the script about how the sproc achieves this. Using the sproc in place of the default process works best when there are several backups to restore each time.

The stored procedure only requires two variables; the name of the secondary database and the default restore job that was created when log shipping was set up. All other work (such as log file locations and file names etc.) is sorted by the sproc itself. The sproc will execute the default restore job whenever it needs to restore a log file, so all customisations to the log shipping process are external of the built-in process. The main benefit of doing this is so that the custom job can be deleted and the default job can be used again without having to recreate log shipping. It also means the default alert job can be continued to be used.

Create the stored procedure and type in any database: I chose msdb because that is where all the built-in log shipping objects are, and because I have a backup process for that database. I suppose you could be cute and create the sproc in the primary database and have it available in the secondary. Then create a SQL Agent job that executes the stored procedure for the frequency you usually have the default job at. Disable the default job so that it is only executed by the custom job.

Total article views: 761 | Views in the last 30 days: 4
Related Articles

Backup & Restoration Script

This script provides very useful information about database backup and restoration.





database backup and restore in sql server 2005

database backup and restore using java


Automating Database Restores

This article describes a way to automatically restore multiple database backups from a directory.


Unable to restore backup

Unable to restore backup

log shipping