How to: Enable Log Shipping (Transact-SQL)

This topic explains how to enable log shipping and add a secondary database using Transact-SQL.

Note

SQL Server 2008 Enterprise and later versions support backup compression. When creating a log shipping configuration, you can control the backup compression behavior of log backups. For more information, see Backup Compression (SQL Server).

Prerequisite: Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server. This is a share of the directory where the transaction log backups will be generated. For example, if you back up your transaction logs to the directory c:\data\tlogs\, you could create the \\primaryserver\tlogs share off that directory.

To enable log shipping

  1. Initialize the secondary database by restoring a full backup of the primary database on the secondary server.

  2. On the primary server, execute sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.

  3. On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.

  4. On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.

  5. On the primary server, enable the backup job.

  6. On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.

  7. On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.

  8. On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.

  9. On the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.

  10. On the secondary server, enable the copy and restore jobs.