Configure Log Shipping
准备工作
两台装有的Windows Server 2012R2以及SQL Server 2012的服务器
下载 Microsoft SQL Server 2012 SP1
两台机器可以相互Ping 通，测试环境为了不必要的麻烦请关闭Windows 防护墙
IP:192.168.100.101 Servername: SQL1\SQL1
IP:192.168.100.102 Servername: SQL2\SQL2
开始SQL Server 代理服务 并设置为自动开启
SQL Agent 登入设置:在服务管理中改成使用Administrator，如自建用户请确保两边用户名以及密码相同。并启动服务。
准备共享文件夹
事务日志传送的就是由主库不断产生事务日志文件的备份（或者叫归档日志，可能更好理解）而备库不断还原这些事务日志备份文件的过程。
中间需要一个文件夹作为双方的访问的共享文件夹。
如果这个共享文件夹位于主库的服务器上，主库的备份路径可以不写成UNC路径的形式，而备库则必须写成UNC路径的形式。
如果这个共享文件夹位于备库的服务器上，主库的备份路径就要写成UNC路径，而备库可以写成本地路径的形式。
如果共享文件夹即不在主库也不在备库的服务器上面，那么备份、还原目录的名称都要写成UNC路径了。
在SQL1的C 盘创建一个名为primaryBackupLog的文件夹，并设置为共享文件夹。 NUC:\\SQL1\primaryBackupLog
在SQL2的C 盘创建一个名为secondaryBackup
SQL Server 请使用SQL Server账号进行登录的，
UserName：sa Password 相同
测试环境为了不必要的麻烦，请使用Administrator账号
将SQL1的 AdventureWorks2012 恢复模式改为完整
1 USE [master] 2 GO 3 ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAIT 4 GO
对SQL1的AdventureWorks2012进行全备
1 USE [master] 2 BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\primaryBackupLog\AdventureWorks.BAK' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-FullBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 3 GO
在SQL2上进行还原，并以STANDBY的方式进行恢复
1 USE [master] 2 RESTORE DATABASE [AdventureWorks2012] 3 FROM DISK = N'C:\secondaryBackup\AdventureWorks.BAK' WITH FILE = 1, 4 MOVE N'AdventureWorks2012_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_Data.mdf', 5 MOVE N'AdventureWorks2012_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_log.ldf', 6 STANDBY = N'C:\secondaryBackup\AdventureWorks.BAK_S', 7 NOUNLOAD, STATS = 10 8 GO
设置备份选项
配置复制作业
以下为代码方式实现
1 -- 在主服务器上执行下列语句，以便为数据库 [192.168.100.101\SQL1].[AdventureWorks2012] 2 -- 配置日志传送。 3 -- 需要在主服务器上 [msdb] 数据库的上下文中运行该脚本。 4 ------------------------------------------------------------------------------------- 5 -- 添加日志传送配置 6 7 -- ****** 开始: 要在主服务器 [192.168.100.101\SQL1] 上运行的脚本 ****** 8 9 10 DECLARE @LS_BackupJobId AS uniqueidentifier 11 DECLARE @LS_PrimaryId AS uniqueidentifier 12 DECLARE @SP_Add_RetCode As int 13 14 15 EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 16 @database = N'AdventureWorks2012' 17 ,@backup_directory = N'C:\primaryBackupLog' 18 ,@backup_share = N'\\SQL1\primaryBackupLog' 19 ,@backup_job_name = N'LSBackup_AdventureWorks2012' 20 ,@backup_retention_period = 4320 21 ,@backup_compression = 2 22 ,@backup_threshold = 60 23 ,@threshold_alert_enabled = 1 24 ,@history_retention_period = 5760 25 ,@backup_job_id = @LS_BackupJobId OUTPUT 26 ,@primary_id = @LS_PrimaryId OUTPUT 27 ,@overwrite = 1 28 29 30 IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 31 BEGIN 32 33 DECLARE @LS_BackUpScheduleUID As uniqueidentifier 34 DECLARE @LS_BackUpScheduleID AS int 35 36 37 EXEC msdb.dbo.sp_add_schedule 38 @schedule_name =N'LSBackupSchedule_192.168.100.101\SQL11' 39 ,@enabled = 1 40 ,@freq_type = 4 41 ,@freq_interval = 1 42 ,@freq_subday_type = 4 43 ,@freq_subday_interval = 15 44 ,@freq_recurrence_factor = 0 45 ,@active_start_date = 20141015 46 ,@active_end_date = 99991231 47 ,@active_start_time = 0 48 ,@active_end_time = 235900 49 ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 50 ,@schedule_id = @LS_BackUpScheduleID OUTPUT 51 52 EXEC msdb.dbo.sp_attach_schedule 53 @job_id = @LS_BackupJobId 54 ,@schedule_id = @LS_BackUpScheduleID 55 56 EXEC msdb.dbo.sp_update_job 57 @job_id = @LS_BackupJobId 58 ,@enabled = 1 59 60 61 END 62 63 64 EXEC master.dbo.sp_add_log_shipping_alert_job 65 66 EXEC master.dbo.sp_add_log_shipping_primary_secondary 67 @primary_database = N'AdventureWorks2012' 68 ,@secondary_server = N'192.168.100.102\SQL2' 69 ,@secondary_database = N'AdventureWorks2012' 70 ,@overwrite = 1 71 72 -- ****** 结束: 要在主服务器 [192.168.100.101\SQL1] 上运行的脚本 ****** 73 74 75 -- 在辅助服务器上执行下列语句，以便为数据库 [192.168.100.102\SQL2].[AdventureWorks2012] 76 -- 配置日志传送。 77 -- 需要在辅助服务器上 [msdb] 数据库的上下文中运行该脚本。 78 ------------------------------------------------------------------------------------- 79 -- 添加日志传送配置 80 81 -- ****** 开始: 要在辅助服务器 [192.168.100.102\SQL2] 上运行的脚本 ****** 82 83 84 DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier 85 DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier 86 DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier 87 DECLARE @LS_Add_RetCode As int 88 89 90 EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 91 @primary_server = N'192.168.100.101\SQL1' 92 ,@primary_database = N'AdventureWorks2012' 93 ,@backup_source_directory = N'\\SQL1\primaryBackupLog' 94 ,@backup_destination_directory = N'C:\secondaryBackup' 95 ,@copy_job_name = N'LSCopy_192.168.100.101\SQL1_AdventureWorks2012' 96 ,@restore_job_name = N'LSRestore_192.168.100.101\SQL1_AdventureWorks2012' 97 ,@file_retention_period = 4320 98 ,@overwrite = 1 99 ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 100 ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 101 ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 102 103 IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 104 BEGIN 105 106 DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier 107 DECLARE @LS_SecondaryCopyJobScheduleID AS int 108 109 110 EXEC msdb.dbo.sp_add_schedule 111 @schedule_name =N'DefaultCopyJobSchedule' 112 ,@enabled = 1 113 ,@freq_type = 4 114 ,@freq_interval = 1 115 ,@freq_subday_type = 4 116 ,@freq_subday_interval = 1 117 ,@freq_recurrence_factor = 0 118 ,@active_start_date = 20141015 119 ,@active_end_date = 99991231 120 ,@active_start_time = 0 121 ,@active_end_time = 235900 122 ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 123 ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 124 125 EXEC msdb.dbo.sp_attach_schedule 126 @job_id = @LS_Secondary__CopyJobId 127 ,@schedule_id = @LS_SecondaryCopyJobScheduleID 128 129 DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier 130 DECLARE @LS_SecondaryRestoreJobScheduleID AS int 131 132 133 EXEC msdb.dbo.sp_add_schedule 134 @schedule_name =N'DefaultRestoreJobSchedule' 135 ,@enabled = 1 136 ,@freq_type = 4 137 ,@freq_interval = 1 138 ,@freq_subday_type = 4 139 ,@freq_subday_interval = 1 140 ,@freq_recurrence_factor = 0 141 ,@active_start_date = 20141015 142 ,@active_end_date = 99991231 143 ,@active_start_time = 0 144 ,@active_end_time = 235900 145 ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 146 ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 147 148 EXEC msdb.dbo.sp_attach_schedule 149 @job_id = @LS_Secondary__RestoreJobId 150 ,@schedule_id = @LS_SecondaryRestoreJobScheduleID 151 152 153 END 154 155 156 DECLARE @LS_Add_RetCode2 As int 157 158 159 IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 160 BEGIN 161 162 EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 163 @secondary_database = N'AdventureWorks2012' 164 ,@primary_server = N'192.168.100.101\SQL1' 165 ,@primary_database = N'AdventureWorks2012' 166 ,@restore_delay = 0 167 ,@restore_mode = 0 168 ,@disconnect_users = 0 169 ,@restore_threshold = 45 170 ,@threshold_alert_enabled = 1 171 ,@history_retention_period = 5760 172 ,@overwrite = 1 173 174 END 175 176 177 IF (@@error = 0 AND @LS_Add_RetCode = 0) 178 BEGIN 179 180 EXEC msdb.dbo.sp_update_job 181 @job_id = @LS_Secondary__CopyJobId 182 ,@enabled = 1 183 184 EXEC msdb.dbo.sp_update_job 185 @job_id = @LS_Secondary__RestoreJobId 186 ,@enabled = 1 187 188 END 189 190 191 -- ****** 结束: 要在辅助服务器 [192.168.100.102\SQL2] 上运行的脚本 ******
在SQL1 上进行测试
1 use AdventureWorks2012
2 CREATE TABLE testtable( number int ,
3 num nvarchar( 50
4 )
5 );
6 INSERT INTO testtable
7 VALUES( 1 ,
8 'aaa'
9 );
10 GO
11 select * from testtable;
Step By Step SQL Server Log Shipping
Configure Log Shipping (SQL Server)
