sp_add_log_shipping_secondary_primary (Transact-SQL)

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 Syntax Conventions

Składnia

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'
  The name of the primary instance of the Microsoft SQL Server Database Engine in the log shipping configuration. primary_server is sysname and cannot be NULL.

• [ @primary_database = ] 'primary_database'
  Is the name of the database on the primary server. primary_database is sysname, with no default.

• [ @backup_source_directory = ] 'backup_source_directory'
  The directory where transaction log backup files from the primary server are stored. backup_source_directory is nvarchar(500) and cannot be NULL.

• [ @backup_destination_directory = ] 'backup_destination_directory'
  The directory on the secondary server where backup files are copied to. backup_destination_directory is nvarchar(500) and cannot be NULL.

• [ @copy_job_name = ] 'copy_job_name'
  The name to use for the SQL Server Agent job being created to copy transaction log backups to the secondary server. copy_job_name is sysname and cannot be NULL.

• [ @restore_job_name = ] 'restore_job_name'
  Is the name of the SQL Server Agent job on the secondary server that restores the backups to the secondary database. restore_job_name is sysname and cannot be NULL.

• [ @file_retention_period = ] 'file_retention_period'
  Is the length of time in minutes in which the history is retained. history_retention_period is int, with a default of NULL. A value of 14420 will be used if none is specified.

• [ @monitor_server = ] 'monitor_server'
  Is the name of the monitor server. Monitor_server is sysname, with no default, and cannot be NULL.

• [ @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 bit and cannot be NULL.

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

• [ @copy_job_id = ] 'copy_job_id' OUTPUT
  The ID associated with the copy job on the secondary server. copy_job_id is uniqueidentifier and cannot be NULL.

• [ @restore_job_id = ] 'restore_job_id' OUTPUT
  The ID associated with the restore job on the secondary server. restore_job_id is uniqueidentifier and cannot be NULL.

• [ @secondary_id = ] 'secondary_id' OUTPUT
  The ID for the secondary server in the log shipping configuration. secondary_id is uniqueidentifier and cannot be NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Uwagi

sp_add_log_shipping_secondary_primary must be run from the master database on the secondary server. This stored procedure does the following:

1. Generates a secondary ID for the specified primary server and primary database.

2. Does the following:

   1. Adds an entry for the secondary ID in log_shipping_secondary using the supplied arguments.

   2. Creates a copy job for the secondary ID that is disabled.

   3. Sets the copy job ID in the log_shipping_secondary entry to the job ID of the copy job.

   4. Creates a restore job for the secondary ID that is disabled.

   5. Set the restore job ID in the log_shipping_secondary entry to the job ID of the restore job.

Permissions

Only members of the sysadmin fixed server role can run this procedure.

Examples

This example illustrates using the sp_add_log_shipping_secondary_primary stored procedure to set up information for the primary database AdventureWorks2012 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

Zobacz także

Odwołanie

System Stored Procedures (Transact-SQL)

Koncepcje

About Log Shipping (SQL Server)