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:
- How to perform SQL Server Log Shipping
- INF: Frequently Asked Questions - SQL Server 2000 - Log Shipping
- Log Shipping in SQL Server 2000
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.