Sets up the primary database for a log shipping configuration, including the backup job, local monitor record, and remote monitor record.
Transact-SQL Syntax Conventions
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 ]
[ @database = ] ' database'
Is the name of the log shipping primary database.
database is , with no default, and cannot be NULL. sysname [ @backup_directory = ] ' backup_directory'
Is the path to the backup folder on the primary server.
backup_directory is , with no default, and cannot be NULL. nvarchar(500) [ @backup_share = ] ' backup_share'
Is the network path to the backup directory on the primary server.
backup_share is , with no default, and cannot be NULL. nvarchar(500) [ @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_name is and cannot be NULL. sysname [ @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_period is , with no default, and cannot be NULL. int [ @monitor_server = ] ' monitor_server'
Is the name of the monitor server.
Monitor_server is , with no default, and cannot be NULL. sysname [ @monitor_server_security_mode = ] monitor_server_security_mode
The security mode used to connect to the monitor server.
1 = Windows Authentication.
0 = SQL Server Authentication.
monitor_server_security_mode is and cannot be NULL. bit [ @monitor_server_login = ] ' monitor_server_login'
Is the username of the account used to access the monitor server.
[ @monitor_server_password = ] ' monitor_server_password'
Is the password of the account used to access the monitor server.
[ @backup_threshold = ] backup_threshold
Is the length of time, in minutes, after the last backup before a
threshold_alert error is raised. backup_threshold is , with a default of 60 minutes. int [ @threshold_alert = ] threshold_alert
Is the alert to be raised when the backup threshold is exceeded.
threshold_alert is , with a default of 14,420. int [ @threshold_alert_enabled = ] threshold_alert_enabled
Specifies whether an alert will be raised when
backup_threshold is exceeded. The value of zero (0), the default, means that the alert is disabled and will not be raised. threshold_alert_enabled is . bit [ @history_retention_period = ] history_retention_period
Is the length of time in minutes in which the history will be retained.
history_retention_period is , with a default of NULL. A value of 14420 will be used if none is specified. int [ @backup_job_id = ] backup_job_id OUTPUT
The SQL Server Agent job ID associated with the backup job on the primary server.
backup_job_id is and cannot be NULL. uniqueidentifier [ @primary_id = ] primary_id OUTPUT
The ID of the primary database for the log shipping configuration.
primary_id is and cannot be NULL. uniqueidentifier [ @backup_compression= ] backup_compression_option
Specifies whether a log shipping configuration uses
backup compression. This parameter is supported only in SQL Server 2008 Enterprise (or a later version).
0 = Disabled. Never compress log backups.
1 = Enabled. Always compress log backups.
2 = Use the setting of the
backup compression default server-configuration option. This is the default value.
0 (success) or 1 (failure)
sp_add_log_shipping_primary_database must be run from the master database on the primary server. This stored procedure performs the following functions:
Generates a primary ID and adds an entry for the primary database in the table
log_shipping_primary_databases using the supplied arguments.
Creates a backup job for the primary database that is disabled.
Sets the backup job ID in the
log_shipping_primary_databases entry to the job ID of the backup job.
Adds a local monitor record in the table
log_shipping_monitor_primary on the primary server using supplied arguments.
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.
Only members of the
sysadmin fixed server role can run this procedure.
This example adds the database AdventureWorks as the primary database in a log shipping configuration.
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
@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
Reference Concepts Other Resources