sp_change_log_shipping_primary_database (Transact-SQL)sp_change_log_shipping_primary_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

更改主数据库设置。Changes the primary database settings.

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

语法Syntax

  
sp_change_log_shipping_primary_database [ @database = ] 'database'  
[, [ @backup_directory = ] 'backup_directory']   
[, [ @backup_share = ] 'backup_share']   
[, [ @backup_retention_period = ] 'backup_retention_period']  
[, [ @monitor_server_security_mode = ] 'monitor_server_security_mode']  
[, [ @monitor_server_login = ] 'monitor_server_login']  
[, [ @monitor_server_password = ] 'monitor_server_password']  
[, [ @backup_threshold = ] 'backup_threshold']   
[, [ @threshold_alert = ] 'threshold_alert']   
[, [ @threshold_alert_enabled = ] 'threshold_alert_enabled']   
[, [ @history_retention_period = ] 'history_retention_period']  
[, [ @backup_compression = ] backup_compression_option ]   

参数Arguments

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

[ @backup_directory = ] 'backup_directory'主服务器上备份文件夹的路径。[ @backup_directory = ] 'backup_directory' Is the path to the backup folder on the primary server. backup_directorynvarchar (500),无默认值,且不能为 NULL。backup_directory is nvarchar(500), with no default, and cannot be NULL.

[ @backup_share = ] 'backup_share'主服务器上的备份目录的网络路径。[ @backup_share = ] 'backup_share' Is the network path to the backup directory on the primary server. backup_sharenvarchar (500),无默认值,且不能为 NULL。backup_share is nvarchar(500), with no default, and cannot be NULL.

[ @backup_retention_period = ] 'backup_retention_period'在主服务器上的备份目录中保留日志备份文件的时间长度(以分钟为单位)。[ @backup_retention_period = ] 'backup_retention_period' Is the length of time, in minutes, to retain the log backup file in the backup directory on the primary server. backup_retention_periodint,没有默认值,且不能为 NULL。backup_retention_period is int, 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 Server 身份验证。0 = SQL 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.

[ @backup_threshold = ] 'backup_threshold'在引发threshold_alert错误之前,最后一次备份之后的时间长度(以分钟为单位)。[ @backup_threshold = ] 'backup_threshold' Is the length of time, in minutes, after the last backup before a threshold_alert error is raised. backup_threshold的值为int,默认值为60分钟。backup_threshold is int, with a default of 60 minutes.

[ @threshold_alert = ] 'threshold_alert'超过备份阈值时引发的警报。[ @threshold_alert = ] 'threshold_alert' The alert to be raised when the backup threshold is exceeded. threshold_alertint ,且不能为 NULL。threshold_alert is int and cannot be NULL.

[ @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 = 启用。1 = enabled.

0 = 禁用。0 = disabled.

threshold_alert_enabledbit ,并且不能为 NULL。threshold_alert_enabled is bit and cannot be NULL.

[ @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_periodint。如果未指定值14420,则使用该值。history_retention_period is int. A value of 14420 is used if none is specified.

[ @backup_compression = ] backup_compression_option指定日志传送配置是否使用备份压缩[ @backup_compression = ] backup_compression_option Specifies whether a log shipping configuration uses backup compression. 只有 SQL Server 2008 EnterpriseSQL Server 2008 Enterprise(或更高版本)支持此参数。This parameter is supported only in SQL Server 2008 EnterpriseSQL Server 2008 Enterprise (or a later version).

0 = 禁用。0 = Disabled. 从不压缩日志备份。Never compress log backups.

1 = 启用。1 = Enabled. 始终压缩日志备份。Always compress log backups.

2 = 使用视图的设置或配置备份压缩默认服务器配置选项2 = Use the setting of the View or Configure the backup compression default Server Configuration Option. 这是默认值。This is the default value.

返回代码值Return Code Values

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

结果集Result Sets

None

备注Remarks

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

  1. 如有必要,更改log_shipping_primary_database记录中的设置。Changes the settings in the log_shipping_primary_database record, if necessary.

  2. 必要时,使用提供的参数更改主服务器上log_shipping_monitor_primary中的本地记录。Changes the local record in log_shipping_monitor_primary on the primary server using supplied arguments, if necessary.

  3. 如果监视服务器与主服务器不同,则使用提供的参数更改监视服务器log_shipping_monitor_primary中的记录(如有必要)。If the monitor server is different from the primary server, changes record in log_shipping_monitor_primary on the monitor server using supplied arguments, if necessary.

权限Permissions

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

示例Examples

此示例演示如何使用sp_change_log_shipping_primary_database来更新与主数据库AdventureWorks2012AdventureWorks2012关联的设置。This example illustrates the use of sp_change_log_shipping_primary_database to update the settings associated with the primary database AdventureWorks2012AdventureWorks2012.

EXEC master.dbo.sp_change_log_shipping_primary_database   
 @database = N'AdventureWorks'   
, @backup_directory = N'c:\LogShipping'   
, @backup_share = N'\\tribeca\LogShipping'   
, @backup_retention_period = 1440   
, @backup_threshold = 60   
, @threshold_alert = 0   
, @threshold_alert_enabled = 1   
, @history_retention_period = 1440   
,@monitor_server_security_mode = 1  
,@backup_compression = 1;  

另请参阅See Also

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