sp_add_log_shipping_secondary_primary (Transact-SQL)sp_add_log_shipping_secondary_primary (Transact-SQL)

适用于: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

为指定的主数据库设置主服务器信息,添加本地和远程监视器链接,并在辅助服务器上创建复制作业和还原作业。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 语法约定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'日志传送配置中的MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine主实例的名称。[ @primary_server = ] 'primary_server' The name of the primary instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine in the log shipping configuration. primary_serversysname ,且不能为 NULL。primary_server is sysname and cannot be NULL.

[ @primary_database = ] 'primary_database'主服务器上的数据库的名称。[ @primary_database = ] 'primary_database' Is the name of the database on the primary server. primary_database sysname,无默认值。primary_database is sysname, with no default.

[ @backup_source_directory = ] 'backup_source_directory'存储主服务器中的事务日志备份文件的目录。[ @backup_source_directory = ] 'backup_source_directory' The directory where transaction log backup files from the primary server are stored. backup_source_directorynvarchar (500) ,且不能为 NULL。backup_source_directory is nvarchar(500) and cannot be NULL.

[ @backup_destination_directory = ] 'backup_destination_directory'辅助服务器上将备份文件复制到的目录。[ @backup_destination_directory = ] 'backup_destination_directory' The directory on the secondary server where backup files are copied to. backup_destination_directorynvarchar (500) ,且不能为 NULL。backup_destination_directory is nvarchar(500) and cannot be NULL.

[ @copy_job_name = ] 'copy_job_name'要创建的用于将事务SQL ServerSQL Server日志备份复制到辅助服务器的代理作业的名称。[ @copy_job_name = ] 'copy_job_name' The name to use for the SQL ServerSQL Server Agent job being created to copy transaction log backups to the secondary server. copy_job_namesysname ,且不能为 NULL。copy_job_name is sysname and cannot be NULL.

[ @restore_job_name = ] 'restore_job_name'辅助服务器上的SQL ServerSQL Server代理作业的名称,可将备份还原到辅助数据库。[ @restore_job_name = ] 'restore_job_name' Is the name of the SQL ServerSQL Server Agent job on the secondary server that restores the backups to the secondary database. restore_job_namesysname ,且不能为 NULL。restore_job_name is sysname and cannot be NULL.

[ @file_retention_period = ] 'file_retention_period'备份文件在删除前在@backup_destination_directory参数指定的路径中保留在辅助服务器上的时间长度(以分钟为单位)。[ @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的值为int,默认值为 NULL。history_retention_period is int, with a default of NULL. 如果未指定值,则使用值 14420。A value of 14420 will be used if none is specified.

[ @monitor_server = ] 'monitor_server'监视服务器的名称。[ @monitor_server = ] 'monitor_server' Is the name of the monitor server. Monitor_server sysname,无默认值,且不能为 NULL。Monitor_server is sysname, with no default, and cannot be NULL.

[ @monitor_server_security_mode = ] 'monitor_server_security_mode'用于连接到监视服务器的安全模式。[ @monitor_server_security_mode = ] 'monitor_server_security_mode' The security mode used to connect to the monitor server.

1 = Windows 身份验证。1 = Windows Authentication.

0 = SQL ServerSQL Server 身份验证。0 = SQL ServerSQL Server authentication.

monitor_server_security_modebit ,并且不能为 NULL。monitor_server_security_mode is bit and cannot be NULL.

[ @monitor_server_login = ] 'monitor_server_login'用于访问监视服务器的帐户的用户名。[ @monitor_server_login = ] 'monitor_server_login' Is the username of the account used to access the monitor server.

[ @monitor_server_password = ] 'monitor_server_password'用于访问监视服务器的帐户的密码。[ @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与辅助服务器上的复制作业关联的 ID。[ @copy_job_id = ] 'copy_job_id' OUTPUT The ID associated with the copy job on the secondary server. copy_job_iduniqueidentifier ,且不能为 NULL。copy_job_id is uniqueidentifier and cannot be NULL.

[ @restore_job_id = ] 'restore_job_id' OUTPUT与辅助服务器上的还原作业关联的 ID。[ @restore_job_id = ] 'restore_job_id' OUTPUT The ID associated with the restore job on the secondary server. restore_job_iduniqueidentifier ,且不能为 NULL。restore_job_id is uniqueidentifier and cannot be NULL.

[ @secondary_id = ] 'secondary_id' OUTPUT日志传送配置中辅助服务器的 ID。[ @secondary_id = ] 'secondary_id' OUTPUT The ID for the secondary server in the log shipping configuration. secondary_iduniqueidentifier ,且不能为 NULL。secondary_id is uniqueidentifier and cannot be NULL.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

None

备注Remarks

必须从辅助服务器上的master数据库运行sp_add_log_shipping_secondary_primarysp_add_log_shipping_secondary_primary must be run from the master database on the secondary server. 此存储过程执行以下操作:This stored procedure does the following:

  1. 为指定的主服务器和主数据库生成一个辅助 ID。Generates a secondary ID for the specified primary server and primary database.

  2. 执行以下操作:Does the following:

    1. 使用提供的参数在log_shipping_secondary中添加辅助 ID 的条目。Adds an entry for the secondary ID in log_shipping_secondary using the supplied arguments.

    2. 为禁用的辅助 ID 创建一个复制作业。Creates a copy job for the secondary ID that is disabled.

    3. log_shipping_secondary条目中的复制作业 id 设置为复制作业的作业 id。Sets the copy job ID in the log_shipping_secondary entry to the job ID of the copy job.

    4. 为禁用的辅助 ID 创建一个还原作业。Creates a restore job for the secondary ID that is disabled.

    5. log_shipping_secondary条目中的还原作业 id 设置为还原作业的作业 id。Set the restore job ID in the log_shipping_secondary entry to the job ID of the restore job.

权限Permissions

只有sysadmin固定服务器角色的成员才能运行此过程。Only members of the sysadmin fixed server role can run this procedure.

示例Examples

此示例演示如何使用sp_add_log_shipping_secondary_primary存储过程来设置辅助服务器上的主数据库AdventureWorks2012AdventureWorks2012的信息。This example illustrates using the sp_add_log_shipping_secondary_primary stored procedure to set up information for the primary database AdventureWorks2012AdventureWorks2012 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

关于日志传送 (SQL Server) About Log Shipping (SQL Server)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)