13.2.5 通过T-SQL命令来部署日志传送
SQL Server还提供以T-SQL命令方式来部署日志传送。在上一个章节中当完成步骤（6）或者（8）后，可保存生成的命令脚本。这个脚本命令结果和在Management Studio中操作的结果是等价的，此命令脚本也可以使用在用户在灾难恢复方案中更快速的重新部署日志传送。
得到的命令脚本（带监视服务器）：
-- 在主服务器上执行下列语句，以便为数据库 [SQLSVR1].[DB01]
-- 配置日志传送。
-- 需要在主服务器上 [msdb] 数据库的上下文中运行该脚本。
-------------------------------------------------------------------------------------
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
-- 添加日志传送配置
-- ****** 开始: 要在主服务器 [SQLSVR1] 上运行的脚本 ******
DECLARE
@LS_BackupJobId
AS
uniqueidentifier
DECLARE
@LS_PrimaryId
AS
uniqueidentifier
DECLARE
@SP_Add_RetCode
As
int
EXEC
@SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@
database
= N
'DB01'
,@backup_directory = N
'\\192.168.1.20\backuplog'
,@backup_share = N
'\\192.168.1.20\backuplog'
,@backup_job_name = N
'LSBackup_DB01'
,@backup_retention_period = 4320
,@backup_compression = 2
,@monitor_server = N
'SQLSVR3'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId
OUTPUT
,@primary_id = @LS_PrimaryId
OUTPUT
,@overwrite = 1
IF (@@ERROR = 0
AND
@SP_Add_RetCode = 0)
BEGIN
DECLARE
@LS_BackUpScheduleUID
As
uniqueidentifier
DECLARE
@LS_BackUpScheduleID
AS
int
EXEC
msdb.dbo.sp_add_schedule
@schedule_name =N
'LSBackupSchedule_SQLSVR11'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20140708
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID
OUTPUT
,@schedule_id = @LS_BackUpScheduleID
OUTPUT
EXEC
msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC
msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC
master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N
'DB01'
,@secondary_server = N
'SQLSVR2'
,@secondary_database = N
'DB01'
,@overwrite = 1
-- ****** 结束: 要在主服务器 [SQLSVR1] 上运行的脚本 ******
|
-- 在辅助服务器上执行下列语句，以便为数据库 [SQLSVR2].[DB01]
-- 配置日志传送。
-- 需要在辅助服务器上 [msdb] 数据库的上下文中运行该脚本。
-------------------------------------------------------------------------------------
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
-- 添加日志传送配置
-- ****** 开始: 要在辅助服务器 [SQLSVR2] 上运行的脚本 ******
DECLARE
@LS_Secondary__CopyJobId
AS
uniqueidentifier
DECLARE
@LS_Secondary__RestoreJobId
AS
uniqueidentifier
DECLARE
@LS_Secondary__SecondaryId
AS
uniqueidentifier
DECLARE
@LS_Add_RetCode
As
int
EXEC
@LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N
'SQLSVR1'
,@primary_database = N
'DB01'
,@backup_source_directory = N
'\\192.168.1.20\backuplog'
,@backup_destination_directory = N
'd:\copylog'
,@copy_job_name = N
'LSCopy_SQLSVR1_DB01'
,@restore_job_name = N
'LSRestore_SQLSVR1_DB01'
,@file_retention_period = 4320
,@monitor_server = N
'SQLSVR3'
,@monitor_server_security_mode = 1
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId
OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId
OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId
OUTPUT
IF (@@ERROR = 0
AND
@LS_Add_RetCode = 0)
BEGIN
DECLARE
@LS_SecondaryCopyJobScheduleUID
As
uniqueidentifier
DECLARE
@LS_SecondaryCopyJobScheduleID
AS
int
EXEC
msdb.dbo.sp_add_schedule
@schedule_name =N
'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20140708
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID
OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID
OUTPUT
EXEC
msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE
@LS_SecondaryRestoreJobScheduleUID
As
uniqueidentifier
DECLARE
@LS_SecondaryRestoreJobScheduleID
AS
int
EXEC
msdb.dbo.sp_add_schedule
@schedule_name =N
'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20140708
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID
OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
OUTPUT
EXEC
msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE
@LS_Add_RetCode2
As
int
IF (@@ERROR = 0
AND
@LS_Add_RetCode = 0)
BEGIN
EXEC
@LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N
'DB01'
,@primary_server = N
'SQLSVR1'
,@primary_database = N
'DB01'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 1
,@ignoreremotemonitor = 1
END
IF (@@error = 0
AND
@LS_Add_RetCode = 0)
BEGIN
EXEC
msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC
msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
-- ****** 结束: 要在辅助服务器 [SQLSVR2] 上运行的脚本 ******
|
-- ****** 开始: 要在监视器 [SQLSVR3] 上运行的脚本 ******
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
EXEC
msdb.dbo.sp_processlogshippingmonitorsecondary
@mode = 1
,@secondary_server = N
'SQLSVR2'
,@secondary_database = N
'DB01'
,@secondary_id = N
''
,@primary_server = N
'SQLSVR1'
,@primary_database = N
'DB01'
,@restore_threshold = 45
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@monitor_server = N
'SQLSVR3'
,@monitor_server_security_mode = 1
-- ****** 结束: 要在监视器 [SQLSVR3] 上运行的脚本 ******
|
这个命令脚本中包含的预定义的存储过程用来设定日志传送的各个部分；
master.dbo.sp_add_log_shipping_primary_database 为主数据库设定日志传送配置，并设定日志传送备份作业。
msdb.dbo.sp_add_schedule 为日志传送设定时间表，或者为复制作业设定时间表，或者为还原作业设定时间表。
msdb.dbo.sp_attach_schedule 把日志传送作业链接到时间表，或者把复制作业链接到时间表，或者把还原作业链接到时间表。
msdb.dbo.sp_update_job 启用事务日志的备份作业、复制作业或者还原作业
master.dbo.sp_add_log_shipping_primary_secondary在主数据库上为指定的辅助数据库设置相关信息。
master.dbo.sp_add_log_shipping_secondary_primary 在辅助数据库上为指定的主数据库设置相关信息、添加本地和远程的监控链接以及创建复制和还原作业。
master.dbo.sp_add_log_shipping_secondary_database 为日志传送设定辅助数据库。
msdb.dbo.sp_processlogshippingmonitorsecondary 在监视服务器上启用对主数据库和辅助数据库监视警报。
本文转自 bannerpei 51CTO博客，原文链接：http://blog.51cto.com/281816327/1598315，如需转载请自行联系原作者
