Problem solve Get help with specific problems with your technologies, process and projects.

Automated script to restore database and transaction logs

I have an automated script to restore a database backup from one SQL Server to another, but I also need to be able to restore the transaction logs. I am especially having trouble with the (device) section of the attempted restores. Do you have an automated SQL version of transaction logs restores? And what do I need to correctly set up the (device) section?

Log shipping has become one of the hottest topics in the SQL Server community to support overall platform high availability. It is typically one of the first contributions by the DBA team to help support the overall business goals. Restoring full database and transaction log backups, as you have mentioned, quickly turns into a log shipping solution supporting an overall high availability solution. Log shipping is simply the process of backing up a database transaction log on a primary SQL Server and applying it to a secondary server. Under this scenario the database recovery model is set to 'Full' to capture all of the database transactions. The high level advantage of log shipping is to maintain two separate data sets and prevent extended down time with data related, hardware or software issues. This is achieved with two separate SQL Servers which are independent of one another, but the transactions are applied in the same manner based on the sequential nature of the database transaction log. If a failure occurs, the DBA would need to recover the secondary database in order to continue business operations with the ability to recover immediately before the failure took place.

With the standard edition of SQL Server, scripts can be written to issue BACKUP LOG and RESTORE LOG statements on the primary and secondary server respectively. With the Enterprise Edition of SQL Server, log shipping is one of the additional offerings with management offerings. A number of options are available so I recommend a Google search for your favorite. Here are a few as a point of reference:

Don't let these scripts discourage you from writing your own scripts and learning about the system tables in the MSDB database that support backup and restore statements. As far as the backup device information is concerned for the restore operation, the values are stored in msdb.dbo.backupmediafamily.physical_device_name column. Your can query this information with a simple SELECT statement.

One final note, with any SQL Server backup, best practices dictate to execute RESTORE VERIFYONLY following each backup, i.e full, differential or transaction log. This is truly the only statement that will provide any level of confidence to ensure the backup will properly restore when needed. Whether the RESTORE is needed one minute or one year after the operation is completed, this command provides at least a reasonable level of confidence, although other items can cause RESTORE failure (poor tape management, natural disaster, etc). Good luck with the log shipping solution!

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.