sp_add_log_shipping_secondary_database (Transact-SQL)sp_add_log_shipping_secondary_database (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 a secondary databases for log shipping.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法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'辅助数据库的名称。[ @secondary_database = ] 'secondary_database' Is the name of the secondary database. secondary_database sysname,无默认值。secondary_database is sysname, with no default.

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

[ @restore_delay = ] 'restore_delay'辅助服务器在还原给定备份文件之前等待的时间,以分钟为单位。[ @restore_delay = ] 'restore_delay' The amount of time, in minutes, that the secondary server waits before restoring a given backup file. restore_delayint ,且不能为 NULL。restore_delay is int and cannot be NULL. 默认值为 0。The default value is 0.

[ @restore_all = ] 'restore_all'如果设置为1,则在还原作业运行时,辅助服务器将还原所有可用的事务日志备份。[ @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_allbit ,并且不能为 NULL。restore_all is bit and cannot be NULL.

[ @restore_mode = ] 'restore_mode'辅助数据库的还原模式。[ @restore_mode = ] 'restore_mode' The restore mode for the secondary database.

0 = 用 NORECOVERY 还原日志。0 = Restore log with NORECOVERY.

1 = 用备用还原日志。1 = restore log with STANDBY.

restorebit ,并且不能为 NULL。restore is bit and cannot be NULL.

[ @disconnect_users = ] 'disconnect_users'如果设置为1,则在执行还原操作时,用户将与辅助数据库断开连接。[ @disconnect_users = ] 'disconnect_users' If set to 1, users are disconnected from the secondary database when a restore operation is performed. 默认值 = 0。Default = 0. 断开连接的用户是bit ,并且不能为 NULL。disconnect users is bit and cannot be NULL.

[ @block_size = ] 'block_size'用作备份设备的块大小的大小(以字节为单位)。[ @block_size = ] 'block_size' The size, in bytes, that is used as the block size for the backup device. block_sizeint ,默认值为-1。block_size is int with a default value of -1.

[ @buffer_count = ] 'buffer_count'备份或还原操作使用的缓冲区总数。[ @buffer_count = ] 'buffer_count' The total number of buffers used by the backup or restore operation. buffer_countint ,默认值为-1。buffer_count is int with a default value of -1.

[ @max_transfer_size = ] 'max_transfer_size'SQL ServerSQL Server向备份设备发出的最大输入或输出请求的大小(以字节为单位)。[ @max_transfer_size = ] 'max_transfer_size' The size, in bytes, of the maximum input or output request which is issued by SQL ServerSQL Server to the backup device. max_transfersizeint ,并且可以为 NULL。max_transfersize is int and can be NULL.

[ @restore_threshold = ] 'restore_threshold'在生成警报之前,还原操作允许间隔的分钟数。[ @restore_threshold = ] 'restore_threshold' The number of minutes allowed to elapse between restore operations before an alert is generated. restore_thresholdint ,且不能为 NULL。restore_threshold is int and cannot be NULL.

[ @threshold_alert = ] 'threshold_alert'超过备份阈值时引发的警报。[ @threshold_alert = ] 'threshold_alert' Is the alert to be raised when the backup threshold is exceeded. threshold_alert的值为int,默认值为14420。threshold_alert is int, with a default of 14,420.

[ @threshold_alert_enabled = ] 'threshold_alert_enabled'指定在超出backup_threshold时是否引发警报。[ @threshold_alert_enabled = ] 'threshold_alert_enabled' Specifies whether an alert is raised when backup_threshold is exceeded. 默认值一 (1) 表示要引发警告。The value of one (1), the default, means that the alert is raised. threshold_alert_enabledbitthreshold_alert_enabled is bit.

[ @history_retention_period = ] 'history_retention_period'保留历史记录的时间长度(分钟)。[ @history_retention_period = ] 'history_retention_period' Is the length of time in minutes in which the history is retained. history_retention_period的值为int,默认值为 NULL。history_retention_period is int, with a default of NULL. 如果不指定值,则使用值 14420。A value of 14420 is used if none is specified.

返回代码值Return Code Values

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

结果集Result Sets

None

备注Remarks

必须从辅助服务器上的master数据库运行sp_add_log_shipping_secondary_databasesp_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 ,以初始化辅助服务器上的主日志传送数据库信息。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. 使用提供的参数在log_shipping_secondary_databases中添加辅助数据库的条目。Adds an entry for the secondary database in log_shipping_secondary_databases using the supplied arguments.

  3. 使用提供的参数在辅助服务器上log_shipping_monitor_secondary中添加本地监视记录。Adds a local monitor record in log_shipping_monitor_secondary on the secondary server using supplied arguments.

  4. 如果监视服务器不同于辅助服务器,则使用提供的参数在监视服务器上log_shipping_monitor_secondary中添加监视记录。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

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

示例Examples

此示例演示如何使用sp_add_log_shipping_secondary_database存储过程将数据库LogShipAdventureWorks添加为日志传送配置中的辅助数据库,主数据库AdventureWorks2012AdventureWorks2012驻留在主服务器 TRIBECA 上。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 AdventureWorks2012AdventureWorks2012 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

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