Applies To: SQL Server 2014, SQL Server 2016 Preview
Sets up the primary information, adds local and remote monitor links, and creates copy and restore jobs on the secondary server for the specified primary database.
Applies to: SQL Server (SQL Server 2008 through current version).
sp_add_log_shipping_secondary_primary [ @primary_server = ] 'primary_server', [ @primary_database = ] 'primary_database', [ @backup_source_directory = ] 'backup_source_directory' , [ @backup_destination_directory = ] 'backup_destination_directory' [ @copy_job_name = ] 'copy_job_name' [ @restore_job_name = ] 'restore_job_name' [, [ @file_retention_period = ] 'file_retention_period'] [, [ @monitor_server = ] 'monitor_server'] [, [ @monitor_server_security_mode = ] 'monitor_server_security_mode'] [, [ @monitor_server_login = ] 'monitor_server_login'] [, [ @monitor_server_password = ] 'monitor_server_password'] [, [ @copy_job_id = ] 'copy_job_id' OUTPUT ] [, [ @restore_job_id = ] 'restore_job_id' OUTPUT ] [, [ @secondary_id = ] 'secondary_id' OUTPUT]
- [ @primary_server = ] 'primary_server'
The name of the primary instance of the Microsoft SQL Server Database Engine 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.
- [ @backup_source_directory = ] 'backup_source_directory'
The directory where transaction log backup files from the primary server are stored. backup_source_directory is nvarchar(500) and cannot be NULL.
- [ @backup_destination_directory = ] 'backup_destination_directory'
The directory on the secondary server where backup files are copied to. backup_destination_directory is nvarchar(500) and cannot be NULL.
- [ @copy_job_name = ] 'copy_job_name'
The name to use for the SQL Server Agent job being created to copy transaction log backups to the secondary server. copy_job_name is sysname and cannot be NULL.
- [ @restore_job_name = ] 'restore_job_name'
Is the name of the SQL Server Agent job on the secondary server that restores the backups to the secondary database. restore_job_name is sysname and cannot be NULL.
- [ @file_retention_period = ] 'file_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 will be used if none is specified.
- [ @monitor_server = ] 'monitor_server'
Is the name of the monitor server. Monitor_server is sysname, with no default, and cannot be NULL.
- [ @monitor_server_security_mode = ] 'monitor_server_security_mode'
The security mode used to connect to the monitor server.
1 = Windows Authentication.
0 = SQL Server authentication.
monitor_server_security_mode is bit and cannot be NULL.
- [ @monitor_server_login = ] 'monitor_server_login'
Is the username of the account used to access the monitor server.
- [ @monitor_server_password = ] 'monitor_server_password'
Is the password of the account used to access the monitor server.
- [ @copy_job_id = ] 'copy_job_id' OUTPUT
The ID associated with the copy job on the secondary server. copy_job_id is uniqueidentifier and cannot be NULL.
- [ @restore_job_id = ] 'restore_job_id' OUTPUT
The ID associated with the restore job on the secondary server. restore_job_id is uniqueidentifier and cannot be NULL.
- [ @secondary_id = ] 'secondary_id' OUTPUT
The ID for the secondary server in the log shipping configuration. secondary_id is uniqueidentifier and cannot be NULL.
0 (success) or 1 (failure)
sp_add_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:
Generates a secondary ID for the specified primary server and primary database.
Does the following:
Adds an entry for the secondary ID in log_shipping_secondary using the supplied arguments.
Creates a copy job for the secondary ID that is disabled.
Sets the copy job ID in the log_shipping_secondary entry to the job ID of the copy job.
Creates a restore job for the secondary ID that is disabled.
Set the restore job ID in the log_shipping_secondary entry to the job ID of the restore job.
Only members of the sysadmin fixed server role can run this procedure.
This example illustrates using the sp_add_log_shipping_secondary_primary stored procedure to set up information for the primary database AdventureWorks2012 on the secondary server.
EXEC master.dbo.sp_add_log_shipping_secondary_primary @primary_server = N'TRIBECA' ,@primary_database = N'AdventureWorks' ,@backup_source_directory = N'\\tribeca\LogShipping' ,@backup_destination_directory = N'' ,@copy_job_name = N'' ,@restore_job_name = N'' ,@file_retention_period = 1440 ,@monitor_server = N'ROCKAWAY' ,@monitor_server_security_mode = 1 ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT ; GO