sp_add_log_shipping_secondary_primary (Transact-SQL)
Applies to:
SQL Server
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.
Transact-SQL syntax conventions
Syntax
sp_add_log_shipping_secondary_primary
[ @primary_server = ] 'primary_server'
, [ @primary_database = ] 'primary_database'
, [ @backup_source_directory = ] N'backup_source_directory'
, [ @backup_destination_directory = ] N'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 SQL Server Database Engine in the log shipping configuration. @primary_server is sysname and can't be NULL.
[ @primary_database = ] 'primary_database'
The name of the database on the primary server. @primary_database is sysname, with no default.
[ @backup_source_directory = ] N'backup_source_directory'
The directory where transaction log backup files from the primary server are stored. @backup_source_directory is nvarchar(500) and can't be NULL.
[ @backup_destination_directory = ] N'backup_destination_directory'
The directory on the secondary server where backup files are copied to. @backup_destination_directory is nvarchar(500) and can't 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 can't be NULL.
[ @restore_job_name = ] 'restore_job_name'
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 can't be NULL.
[ @file_retention_period = ] 'file_retention_period'
The length of time, in minutes, that a backup file is retained on the secondary server in the path specified by the @backup_destination_directory parameter before being deleted. @history_retention_period is int, with a default of NULL
. A value of 14420 is used if none is specified.
[ @monitor_server = ] 'monitor_server'
The name of the monitor server. @monitor_server is sysname, with no default, and can't be NULL.
[ @monitor_server_security_mode = ] 'monitor_server_security_mode'
The security mode used to connect to the monitor server.
1
: Windows Authentication0
: SQL Server authentication
@monitor_server_security_mode is bit, with a default of 1
, and can't be NULL.
[ @monitor_server_login = ] 'monitor_server_login'
The username of the account used to access the monitor server.
[ @monitor_server_password = ] 'monitor_server_password'
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 can't 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 can't be NULL.
[ @secondary_id = ] 'secondary_id' OUTPUT
The ID for the secondary server in the log shipping configuration. @secondary_id is uniqueidentifier and can't be NULL.
Return code values
0
(success) or 1
(failure).
Result set
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:
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.
- Adds an entry for the secondary ID in
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 AdventureWorks2022
on the secondary server.
EXEC master.dbo.sp_add_log_shipping_secondary_primary @primary_server = N'TRIBECA',
@primary_database = N'AdventureWorks2022',
@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
Related content
Tilbakemeldinger
https://aka.ms/ContentUserFeedback.
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se:Send inn og vis tilbakemelding for