Edit

Add a Secondary Database to a Log Shipping Configuration (SQL Server)

  • Article

Applies to: SQL Server

This topic describes how to add a secondary database to an existing log shipping configuration in SQL Server by using SQL Server Management Studio or Transact-SQL.

Before You Begin

Security

Permissions

The log-shipping stored procedures require membership in the sysadmin fixed server role.

Using SQL Server Management Studio

To add a log shipping secondary database

  1. Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

  2. Under Select a page, click Transaction Log Shipping.

  3. Under Secondary server instances and databases, click Add.

  4. Click Connect and connect to the instance of SQL Server that you want to use as your secondary server.

  5. In the Secondary database box, choose a database from the list or type the name of the database you want to create.

  6. On the Initialize Secondary database tab, choose the option that you want to use to initialize the secondary database.

  7. On the Copy Files tab, in the Destination folder for copied files box, type the path of the folder into which the transaction logs backups should be copied. This folder is often located on the secondary server.

  8. Note the copy schedule listed in the Schedule box under Copy job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

  9. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

  10. If you chose the Standby mode option, choose if you want to disconnect users from the secondary database while the restore operation is underway.

  11. If you want to delay the restore process on the secondary server, choose a delay time under Delay restoring backups at least.

  12. Choose an alert threshold under Alert if no restore occurs within.

  13. Note the restore schedule listed in the Schedule box under Restore job. If you want to customize the schedule for your installation, click Schedule and then adjust the SQL Server Agent schedule as needed. This schedule should approximate the backup schedule.

  14. Click OK.

  15. Click OK on the Database Properties dialog box to begin the configuration process.

Using Transact-SQL

To add a log shipping secondary database

  1. 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.

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

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

  4. 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.

  5. On the secondary server, enable the copy and restore jobs. For more information, see Disable or Enable a Job.

Related Tasks

See Also

About Log Shipping (SQL Server)
Log Shipping Tables and Stored Procedures