Configure Log Shipping

准备工作

两台装有的Windows Server 2012R2以及SQL Server 2012的服务器

下载评估版 Windows Server 2012 R2

下载 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 GO3 ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAIT4 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 = 103 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 = 108 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 15