How to: Enable Log Shipping (Transact-SQL)
This topic explains how to enable log shipping and add a secondary database using Transact-SQL.
Note
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 using.
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
Tasks
How to: Enable Log Shipping (SQL Server Management Studio)
Concepts
Log Shipping Tables and Stored Procedures
Other Resources
Log Shipping
sp_add_log_shipping_primary_database (Transact-SQL)
sp_add_jobschedule (Transact-SQL)
sp_add_log_shipping_alert_job (Transact-SQL)
sp_add_log_shipping_secondary_primary (Transact-SQL)
sp_add_log_shipping_secondary_database (Transact-SQL)
sp_add_log_shipping_primary_secondary (Transact-SQL)