同步备用服务器

同步备用服务器

 /*--说明：  
  下面的代码演示了如何利用日志还原功能，将主数据库中的数据变化及时反馈到备用数据库中  
  备用数据库的数据可以随时用于查询，但不能被更新（备用数据库只读）。  
  --*/  
   
  --首先,创建一个演示用的数据库(主数据库)  
  CREATE   DATABASE   Db_test  
  ON  
  (   NAME   =   Db_test_DATA,  
              FILENAME   =   'c:/Db_test.mdf'   )  
  LOG   ON    
  (   NAME   =   Db_test_LOG,  
        FILENAME   =   'c:/Db_test.ldf')  
  GO  
   
  --对数据库进行备份  
  BACKUP   DATABASE   Db_test   TO   DISK='c:/test_data.bak'   WITH   FORMAT  
  GO  
   
  --把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步)  
  RESTORE   DATABASE   Db_test_bak   FROM   DISK='c:/test_data.bak'    
  WITH   REPLACE,STANDBY='c:/db_test_bak.ldf'  
  ,MOVE   'Db_test_DATA'   TO   'c:/Db_test_data.mdf'  
  ,MOVE   'Db_test_LOG'   TO   'c:/Db_test_log.ldf'  
  GO  
   
  --启动   SQL   Agent   服务  
  EXEC   master..xp_cmdshell   'net   start   sqlserveragent',no_output  
  GO  
   
  --创建主服务器数据库与备用服务器数据库之间同步的作业  
  DECLARE     @jogid   uniqueidentifier  
  EXEC   msdb..sp_add_job  
  @job_id   =   @jogid   OUTPUT,  
  @job_name   =   N'数据同步处理'  
   
  --创建同步处理步骤  
  EXEC   msdb..sp_add_jobstep    
  @job_id   =   @jogid,  
  @step_name   =   N'数据同步',  
  @subsystem   =   'TSQL',  
  @command   =   N'  
  --主数据库中进行日志备份  
  BACKUP   LOG   Db_test   TO   DISK='c:/test_log.bak'  WITH   FORMAT   
注：当备份日志出现“当恢复模型为 SIMPLE 时,不允许使用 BACKUP LOG 语句”，进入MSSQL的企业管理器,选择相应的数据库实体,点击右键选择"属性",选择"选项"标签页,把故障还原模型修改成"大容量日志记录的",然后重新备份一下!
    
  --备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化  
  --实际应该是主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立，并且备份文件应该放在主服务器和备用都能访问的共享目录中  
  RESTORE   LOG   Db_test_bak   FROM   DISK='c:/test_log.bak'   WITH   STANDBY='c:/test_log.ldf',  
  @retry_attempts   =   5,  
  @retry_interval   =   5  

  --创建调度(每分钟执行一次)  
  EXEC   msdb..sp_add_jobschedule  
  @job_id   =   @jogid,  
  @name   =   N'时间安排',  
  @freq_type=4,  
  @freq_interval=1,  
  @freq_subday_type=0x4,  
  @freq_subday_interval=1,  
  @freq_recurrence_factor=1  
   
  --   添加目标服务器  
  EXEC   msdb.dbo.sp_add_jobserver    
  @job_id   =   @jogid,  
  @server_name   =   N'(local)'    
  GO  
   
  --通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成  
  --下面开始测试是否能实现同步  
   
  --在主数据库中创建一个测试用的表  
  CREATE   TABLE   Db_test.dbo.TB_test(ID   int)  
  GO  
   
  --等待1分钟30秒（由于同步的时间间隔设置为1分钟,所以要延时才能看到效果）  
  WAITFOR   DELAY   '00:01:30'  
  GO  
   
  --查询一下备用数据库,看看同步是否成功  
  SELECT   *   FROM   Db_test_bak.dbo.TB_test  
   
  /*--结果:  
  ID                      
  -----------    
   
  （所影响的行数为   0   行）  
  --*/  
   
  --测试成功  
  GO  
   
  --最后删除所有的测试  
  DROP   DATABASE   Db_test,Db_test_bak  
  EXEC   msdb..sp_delete_job   @job_name=N'数据同步处理'  
  GO  
   
   
  /*===========================================================*/  
   
  /*--服务器档机处理说明  
  使用这种方式建立的数据库同步，当主数据库不可用时（例如，主数据库损坏或者停机检修）  
  可以使用以下两种方法使备用数据库可用。  
  --*/  
   
  --1.   如果主数据库损坏，无法备份出最新的日志，可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）。  
  --RESTORE   LOG   Db_test_bak   WITH   RECOVERY  
   
   
  --2.   如果主数据库可以备份出最新日志，则可以使用下面的语句。  
  --先备份主数据库的最新的事务日志  
  --BACKUP   LOG   Db_test   TO   DISK=''c:/test_log.bak''   WITH   FORMAT  
  --再在备用数据库中恢复最新的事务日志，并且使备用数据库可读写（升级为主数据库）  
  --RESTORE   LOG   Db_test_bak   FROM   DISK='c:/test_log.bak'