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
Initialize the secondary database by restoring a full backup of the primary database on the secondary server.
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.
On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.
On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.
On the primary server, enable the backup job.
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.
On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
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.
On the secondary server, enable the copy and restore jobs.
See Also