sp_add_log_shipping_secondary_primary (Transact-SQL)

  • Article

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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]

Arguments

  • [ @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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_add_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:

  1. Generates a secondary ID for the specified primary server and primary database.

  2. Does the following:

    1. Adds an entry for the secondary ID in log_shipping_secondary using the supplied arguments.

    2. Creates a copy job for the secondary ID that is disabled.

    3. Sets the copy job ID in the log_shipping_secondary entry to the job ID of the copy job.

    4. Creates a restore job for the secondary ID that is disabled.

    5. Set the restore job ID in the log_shipping_secondary entry to the job ID of the restore job.

Permissions

Only members of the sysadmin fixed server role can run this procedure.

Examples

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

See Also

Reference

System Stored Procedures (Transact-SQL)

Concepts

About Log Shipping (SQL Server)