--
--6.6.4节示例
--
--备份和还原数据库
backup database logTrans1 --在主数据库上备份
to disk='c:\logt.bak'
--以下是将数据库还原到辅助数据库上
restore database logTrans2
from disk='c:\logt.bak'
with NORECOVERY,
move 'logTrans' to 'c:\logTrans2.mdf',
move 'logTrans_log' to 'c:\logTrans2.ldf'
--配置日志传送主数据库
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
--配置主数据库
EXEC master.dbo.sp_add_log_shipping_primary_database 配置主数据库
@database = N'logTrans1'
,@backup_directory = N'D:\data'
,@backup_share = N'\\10.101.10.66\data'
,@backup_job_name = N'LSBackup_logTrans1'
,@backup_retention_period = 1440
,@monitor_server = N'localhost'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 0
,@history_retention_period = 1440
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
--添加备份计划
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name =N'LSBackup_logTrans1', --SQL作业计划
@name=N'BackupDBEvery2Min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080622,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_id = @schedule_id OUTPUT
select @schedule_id
--添加警报作业
USE master
GO
EXEC sp_add_log_shipping_alert_job;
--启用备份作业
EXEC msdb.dbo.sp_update_job
@job_name='LSBackup_logTrans1',
@enabled=1
--设置复制和还原作业
DECLARE @LS_Secondary__CopyJobId uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId uniqueidentifier
DECLARE @LS_Secondary__SecondaryId uniqueidentifier
EXEC master.dbo.sp_add_log_shipping_secondary_primary --设置复制和还原作业
@primary_server = N'10.101.10.66'
,@primary_database = N'logTrans1'
,@backup_source_directory = N'\\10.101.10.66\data'
,@backup_destination_directory = N'D:\log'
,@copy_job_name = N'LSCopy_logTrans1'
,@restore_job_name = N'LSRestore_logTrans2'
,@file_retention_period = 1440
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
--设置复制作业的计划
DECLARE @schedule_id int
--设置复制作业计划
EXEC msdb.dbo.sp_add_jobschedule
@job_name=N'LSCopy_logTrans1',
@name=N'CopyEvery2Min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080622,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_id = @schedule_id OUTPUT
select @schedule_id
--设置还原作业的计划
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule --设置还原作业的计划
@job_name=N'LSCopy_logTrans1',
@name=N'RestoreEvery2Min',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080622,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_id = @schedule_id OUTPUT
select @schedule_id
--添加辅助数据库
EXEC master.dbo.sp_add_log_shipping_secondary_database --添加辅助数据库
@secondary_database = N'logTrans2'
,@primary_server = N'10.101.10.66'
,@primary_database = N'logTrans1'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 0
,@history_retention_period = 1440
GO
--向主服务器添加辅助数据库的必须信息
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'logTrans1'
, @secondary_server = N'10.101.10.67' --辅助数据库的IP
, @secondary_database = N'logTrans2'
--启用复制和还原作业
EXEC msdb.dbo.sp_update_job --启用复制作业
@job_name='LSCopy_logTrans1',
@enabled=1
EXEC msdb.dbo.sp_update_job --启用还原作业
@job_name='LSRestore_logTrans2',
@enabled=1
