--- title: "Add a Secondary Database to a Log Shipping Configuration (SQL Server) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql-server-2016" ms.reviewer: "" ms.suite: "" ms.technology: - "dbe-high-availability" ms.tgt_pltfrm: "" ms.topic: "article" helpviewer_keywords: - "adding secondary databases" - "secondary databases [SQL Server], in log shipping" - "secondary data files [SQL Server], adding" - "log shipping [SQL Server], secondary databases" ms.assetid: b02eba13-f8e6-4684-b7e4-75ea038ea473 caps.latest.revision: 20 author: "MikeRayMSFT" ms.author: "mikeray" manager: "jhubbard" --- # Add a Secondary Database to a Log Shipping Configuration (SQL Server) This topic describes how to add a secondary database to an existing log shipping configuration in [!INCLUDE[ssCurrent](../../includes/sscurrent-md.md)] by using [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] or [!INCLUDE[tsql](../../includes/tsql-md.md)]. **In This Topic** - **Before you begin:** [Security](#Security) - **To add a log shipping secondary database, using:** [SQL Server Management Studio](#SSMSProcedure) [Transact-SQL](#TsqlProcedure) - [Related Tasks](#RelatedTasks) ## 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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](../../relational-databases/system-stored-procedures/sp-add-log-shipping-secondary-primary-transact-sql.md) 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](../../relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql.md) to set the schedule for the copy and restore jobs. 3. On the secondary server, execute [sp_add_log_shipping_secondary_database](../../relational-databases/system-stored-procedures/sp-add-log-shipping-secondary-database-transact-sql.md) to add a secondary database. 4. On the primary server, execute [sp_add_log_shipping_primary_secondary](../../relational-databases/system-stored-procedures/sp-add-log-shipping-primary-secondary-transact-sql.md) 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](http://msdn.microsoft.com/library/5041261f-0c32-4d4a-8bee-59a6c16200dd). ## Related Tasks - [Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)](../../database-engine/log-shipping/upgrading-log-shipping-to-sql-server-2016-transact-sql.md) - [Configure Log Shipping (SQL Server)](../../database-engine/log-shipping/configure-log-shipping-sql-server.md) - [Remove a Secondary Database from a Log Shipping Configuration (SQL Server)](../../database-engine/log-shipping/remove-a-secondary-database-from-a-log-shipping-configuration-sql-server.md) - [Remove Log Shipping (SQL Server)](../../database-engine/log-shipping/remove-log-shipping-sql-server.md) - [View the Log Shipping Report (SQL Server Management Studio)](../../database-engine/log-shipping/view-the-log-shipping-report-sql-server-management-studio.md) - [Monitor Log Shipping (Transact-SQL)](../../database-engine/log-shipping/monitor-log-shipping-transact-sql.md) - [Fail Over to a Log Shipping Secondary (SQL Server)](../../database-engine/log-shipping/fail-over-to-a-log-shipping-secondary-sql-server.md) ## See Also [About Log Shipping (SQL Server)](../../database-engine/log-shipping/about-log-shipping-sql-server.md) [Log Shipping Tables and Stored Procedures](../../database-engine/log-shipping/log-shipping-tables-and-stored-procedures.md)