准备工作
两台装有的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 恢复模式改为完整
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL WITH NO_WAIT
GO
对SQL1的AdventureWorks2012进行全备
USE [master]
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\primaryBackupLog\AdventureWorks.BAK' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-FullBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
在SQL2上进行还原，并以STANDBY的方式进行恢复
USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'C:\secondaryBackup\AdventureWorks.BAK' WITH FILE = 1,
MOVE N'AdventureWorks2012_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_Data.mdf',
MOVE N'AdventureWorks2012_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_log.ldf',
STANDBY = N'C:\secondaryBackup\AdventureWorks.BAK_S',
NOUNLOAD, STATS = 10
GO
设置备份选项
配置复制作业
以下为代码方式实现
-- 在主服务器上执行下列语句，以便为数据库 [192.168.100.101\SQL1].[AdventureWorks2012]
-- 配置日志传送。
-- 需要在主服务器上 [msdb] 数据库的上下文中运行该脚本。
-------------------------------------------------------------------------------------
-- 添加日志传送配置 -- ****** 开始: 要在主服务器 [192.168.100.101\SQL1] 上运行的脚本 ****** 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'AdventureWorks2012'
,@backup_directory = N'C:\primaryBackupLog'
,@backup_share = N'\\SQL1\primaryBackupLog'
,@backup_job_name = N'LSBackup_AdventureWorks2012'
,@backup_retention_period = 4320
,@backup_compression = 2
,@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_192.168.100.101\SQL11'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20141015
,@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_alert_job EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'AdventureWorks2012'
,@secondary_server = N'192.168.100.102\SQL2'
,@secondary_database = N'AdventureWorks2012'
,@overwrite = 1 -- ****** 结束: 要在主服务器 [192.168.100.101\SQL1] 上运行的脚本 ****** -- 在辅助服务器上执行下列语句，以便为数据库 [192.168.100.102\SQL2].[AdventureWorks2012]
-- 配置日志传送。
-- 需要在辅助服务器上 [msdb] 数据库的上下文中运行该脚本。
-------------------------------------------------------------------------------------
-- 添加日志传送配置 -- ****** 开始: 要在辅助服务器 [192.168.100.102\SQL2] 上运行的脚本 ****** 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'192.168.100.101\SQL1'
,@primary_database = N'AdventureWorks2012'
,@backup_source_directory = N'\\SQL1\primaryBackupLog'
,@backup_destination_directory = N'C:\secondaryBackup'
,@copy_job_name = N'LSCopy_192.168.100.101\SQL1_AdventureWorks2012'
,@restore_job_name = N'LSRestore_192.168.100.101\SQL1_AdventureWorks2012'
,@file_retention_period = 4320
,@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 = 1
,@freq_recurrence_factor = 0
,@active_start_date = 20141015
,@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 = 1
,@freq_recurrence_factor = 0
,@active_start_date = 20141015
,@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'AdventureWorks2012'
,@primary_server = N'192.168.100.101\SQL1'
,@primary_database = N'AdventureWorks2012'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@overwrite = 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 -- ****** 结束: 要在辅助服务器 [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;