sp_add_log_shipping_primary_database (Transact-SQL)sp_add_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

设置日志传送配置(包括备份作业、本地监视记录及远程监视记录)的主数据库。Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.

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

语法Syntax

  
sp_add_log_shipping_primary_database [ @database = ] 'database',   
[ @backup_directory = ] 'backup_directory',   
[ @backup_share = ] 'backup_share',   
[ @backup_job_name = ] 'backup_job_name',   
[, [ @backup_retention_period = ] backup_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']  
[, [ @backup_threshold = ] backup_threshold ]   
[, [ @threshold_alert = ] threshold_alert ]   
[, [ @threshold_alert_enabled = ] threshold_alert_enabled ]   
[, [ @history_retention_period = ] history_retention_period ]  
[, [ @backup_job_id = ] backup_job_id OUTPUT ]  
[, [ @primary_id = ] primary_id OUTPUT]  
[, [ @backup_compression = ] backup_compression_option ]  
  

参数Arguments

[ @database = ] 'database'日志传送主数据库的名称。[ @database = ] 'database' Is the name of the log shipping primary database. 数据库sysname,无默认值,且不能为 NULL。database is sysname, with no default, and cannot be NULL.

[ @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_job_name = ] 'backup_job_name'将备份复制到备份文件夹中的主服务器上 SQL Server 代理作业的名称。[ @backup_job_name = ] 'backup_job_name' Is the name of the SQL Server Agent job on the primary server that copies the backup into the backup folder. backup_job_namesysname ,且不能为 NULL。backup_job_name is sysname 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 = ] '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.

[ @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 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 will be raised when backup_threshold is exceeded. 默认值零 (0) 表示警报被禁用,将不会引发警报。The value of zero (0), the default, means that the alert is disabled and will not be 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 will be retained. 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.

[ @backup_job_id = ] backup_job_id OUTPUTSQL ServerSQL Server主服务器上的备份作业相关联的代理作业 ID。[ @backup_job_id = ] backup_job_id OUTPUT The SQL ServerSQL Server Agent job ID associated with the backup job on the primary server. backup_job_iduniqueidentifier ,且不能为 NULL。backup_job_id is uniqueidentifier and cannot be NULL.

[ @primary_id = ] primary_id OUTPUT日志传送配置的主数据库 ID。[ @primary_id = ] primary_id OUTPUT The ID of the primary database for the log shipping configuration. primary_iduniqueidentifier ,且不能为 NULL。primary_id is uniqueidentifier and cannot be NULL.

[ @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_add_log_shipping_primary_databasesp_add_log_shipping_primary_database must be run from the master database on the primary server. 此存储过程可执行以下功能:This stored procedure performs the following functions:

  1. 使用提供的参数生成主 ID 并为表中的主数据库添加条目log_shipping_primary_databasesGenerates a primary ID and adds an entry for the primary database in the table log_shipping_primary_databases using the supplied arguments.

  2. 为被禁用的主数据库创建一个备份作业。Creates a backup job for the primary database that is disabled.

  3. log_shipping_primary_databases条目中的备份作业 id 设置为备份作业的作业 id。Sets the backup job ID in the log_shipping_primary_databases entry to the job ID of the backup job.

  4. 使用提供的参数,在主服务器上的表log_shipping_monitor_primary中添加本地监视记录。Adds a local monitor record in the table log_shipping_monitor_primary on the primary server using supplied arguments.

  5. 如果监视服务器不同于主服务器,则使用提供的参数在监视服务器上log_shipping_monitor_primary中添加一条监视器记录。If the monitor server is different from the primary server, adds a monitor record in log_shipping_monitor_primary on the monitor server using supplied arguments.

权限Permissions

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

示例Examples

此示例将在日志传送配置中添加 AdventureWorks2012AdventureWorks2012 数据库作为主数据库。This example adds the database AdventureWorks2012AdventureWorks2012 as the primary database in a log shipping configuration.

DECLARE @LS_BackupJobId AS uniqueidentifier ;  
DECLARE @LS_PrimaryId AS uniqueidentifier ;  
  
EXEC master.dbo.sp_add_log_shipping_primary_database   
@database = N'AdventureWorks'   
,@backup_directory = N'c:\lsbackup'   
,@backup_share = N'\\tribeca\lsbackup'   
,@backup_job_name = N'LSBackup_AdventureWorks'   
,@backup_retention_period = 1440  
,@monitor_server = N'rockaway'   
,@monitor_server_security_mode = 1   
,@backup_threshold = 60   
,@threshold_alert = 0   
,@threshold_alert_enabled = 0   
,@history_retention_period = 1440   
,@backup_job_id = @LS_BackupJobId OUTPUT   
,@primary_id = @LS_PrimaryId OUTPUT   
,@overwrite = 1   
,@backup_compression = 0;  
GO  

另请参阅See Also

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