--- title: "sp_add_log_shipping_secondary_database (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords: - "sp_add_log_shipping_secondary_database" - "sp_add_log_shipping_secondary_database_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "sp_add_log_shipping_secondary_database" ms.assetid: d29e1c24-3a3c-47a4-a726-4584afa6038a caps.latest.revision: 22 author: "JennieHubbard" ms.author: "jhubbard" manager: "jhubbard" --- # sp_add_log_shipping_secondary_database (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-xxxx-xxxx-xxx_md](../../includes/tsql-appliesto-ss2008-xxxx-xxxx-xxx-md.md)] Sets up a secondary databases for log shipping. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` sp_add_log_shipping_secondary_database [ @secondary_database = ] 'secondary_database', [ @primary_server = ] 'primary_server', [ @primary_database = ] 'primary_database', [, [ @restore_delay = ] 'restore_delay'] [, [ @restore_all = ] 'restore_all'] [, [ @restore_mode = ] 'restore_mode'] [, [ @disconnect_users = ] 'disconnect_users'] [, [ @block_size = ] 'block_size'] [, [ @buffer_count = ] 'buffer_count'] [, [ @max_transfer_size = ] 'max_transfer_size'] [, [ @restore_threshold = ] 'restore_threshold'] [, [ @threshold_alert = ] 'threshold_alert'] [, [ @threshold_alert_enabled = ] 'threshold_alert_enabled'] [, [ @history_retention_period = ] 'history_retention_period'] ``` ## Arguments [ **@secondary_database** = ] '*secondary_database*' Is the name of the secondary database. *secondary_database* is **sysname**, with no default. [ **@primary_server** = ] '*primary_server*' The name of the primary instance of the [!INCLUDE[msCoName](../../includes/msconame-md.md)] [!INCLUDE[ssDEnoversion](../../includes/ssdenoversion-md.md)] in the log shipping configuration. *primary_server* is **sysname** and cannot be NULL. [ **@primary_database** = ] '*primary_database*' Is the name of the database on the primary server. *primary_database* is **sysname**, with no default. [ **@restore_delay** = ] '*restore_delay*' The amount of time, in minutes, that the secondary server waits before restoring a given backup file. *restore_delay* is **int** and cannot be NULL. The default value is 0. [ **@restore_all** = ] '*restore_all*' If set to 1, the secondary server restores all available transaction log backups when the restore job runs. Otherwise, it stops after one file is restored. *restore_all* is **bit** and cannot be NULL. [ **@restore_mode** = ] '*restore_mode*' The restore mode for the secondary database. 0 = Restore log with NORECOVERY. 1 = restore log with STANDBY. *restore* is **bit** and cannot be NULL. [ **@disconnect_users** = ] '*disconnect_users*' If set to 1, users are disconnected from the secondary database when a restore operation is performed. Default = 0. *disconnect* users is **bit** and cannot be NULL. [ **@block_size** = ] '*block_size*' The size, in bytes, that is used as the block size for the backup device. *block_size* is **int** with a default value of -1. [ **@buffer_count** = ] '*buffer_count*' The total number of buffers used by the backup or restore operation. *buffer_count* is **int** with a default value of -1. [ **@max_transfer_size** = ] '*max_transfer_size*' The size, in bytes, of the maximum input or output request which is issued by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to the backup device. *max_transfersize* is **int** and can be NULL. [ **@restore_threshold** = ] '*restore_threshold*' The number of minutes allowed to elapse between restore operations before an alert is generated. *restore_threshold* is **int** and cannot be NULL. [ **@threshold_alert** = ] '*threshold_alert*' Is the alert to be raised when the backup threshold is exceeded. *threshold_alert* is **int**, with a default of 14,420. [ **@threshold_alert_enabled** = ] '*threshold_alert_enabled*' Specifies whether an alert is raised when *backup_threshold* is exceeded. The value of one (1), the default, means that the alert is raised. *threshold_alert_enabled* is **bit**. [ **@history_retention_period** = ] '*history_retention_period*' Is the length of time in minutes in which the history is retained. *history_retention_period* is **int**, with a default of NULL. A value of 14420 is used if none is specified. ## Return Code Values 0 (success) or 1 (failure) ## Result Sets None ## Remarks **sp_add_log_shipping_secondary_database** must be run from the **master** database on the secondary server. This stored procedure does the following: 1. **sp_add_log_shipping_secondary_primary** should be called prior to this stored procedure to initialize the primary log shipping database information on the secondary server. 2. Adds an entry for the secondary database in **log_shipping_secondary_databases** using the supplied arguments. 3. Adds a local monitor record in **log_shipping_monitor_secondary** on the secondary server using supplied arguments. 4. If the monitor server is different from the secondary server, adds a monitor record in **log_shipping_monitor_secondary** on the monitor server using supplied arguments. ## Permissions Only members of the **sysadmin** fixed server role can run this procedure. ## Examples This example illustrates using the **sp_add_log_shipping_secondary_database** stored procedure to add the database **LogShipAdventureWorks** as a secondary database in a log shipping configuration with the primary database [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] residing on the primary server TRIBECA. ``` EXEC master.dbo.sp_add_log_shipping_secondary_database @secondary_database = N'LogShipAdventureWorks' ,@primary_server = N'TRIBECA' ,@primary_database = N'AdventureWorks2012' ,@restore_delay = 0 ,@restore_mode = 1 ,@disconnect_users = 0 ,@restore_threshold = 45 ,@threshold_alert_enabled = 0 ,@history_retention_period = 1440 ; GO ``` ## See Also [About Log Shipping (SQL Server)](../../database-engine/log-shipping/about-log-shipping-sql-server.md) [System Stored Procedures (Transact-SQL)](../../relational-databases/system-stored-procedures/system-stored-procedures-transact-sql.md)