实战:sqlserver 2008 R2容灾方案:另一种思路-2

概述2.通过第三方软件实时传递数据到B数据库服务器 这种类似的软件很多,如Allway Sync之类的软件,间隔同步时间最好实时 .   3.通过下面脚本来实现是否需要还原 下面只是列出需要的脚本文件，没有列出的文件只需新建相同的文件名即可. 3.1 备库上新建几个监控的表:table.txt USE [master] GO if object_id('restorehistoty-suzhou') @H_301_4@

2.通过第三方软件实时传递数据到B数据库服务器

这种类似的软件很多,如Allway Sync之类的软件,间隔同步时间最好实时 .

 

3.通过下面脚本来实现是否需要还原

下面只是列出需要的脚本文件，没有列出的文件只需新建相同的文件名即可.

3.1 备库上新建几个监控的表:table.txt

USE [master]
GO

if object_ID('restorehistoty-suzhou') is not null
drop table [restorehistoty-suzhou]
go

CREATE table [dbo].[restorehistoty-suzhou](
 [ID] [int] IDENTITY(1,1) NOT NulL,
 [backupfile] [nvarchar](255) not null primary key,
 [resdate] [datetime] NulL,

)
GO

ALTER table [dbo].[restorehistoty-suzhou] ADD  DEFAulT (getdate()) FOR [resdate]
GO

if object_ID('pre-suzhou') is not null
drop table [pre-suzhou]
go

CREATE table [dbo].[pre-suzhou](
 [ID] [int] IDENTITY(1,

)
GO

ALTER table [dbo].[pre-suzhou] ADD  DEFAulT (getdate()) FOR [resdate]
GO

 

 

if object_ID('fileList-suzhou') is not null
drop table [fileList-suzhou]
go

CREATE table [dbo].[fileList-suzhou](
 [backupfile] [nvarchar](255) NulL
)

GO

 

create table [restorehistoty-suzhou-tsql]
(
tsql nvarchar(max)
)

 

3.2 openshell.txt

--开xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',1
GO
-- To update the currently configured value for advanced options.
RECONfigURE WITH OVERRIDE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell',1
GO
-- To update the currently configured value for this feature.
RECONfigURE WITH OVERRIDE
GO
-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options',0
GO
-- To update the currently configured value for advanced options.
RECONfigURE WITH OVERRIDE
GO

3.3 in.txt

set nocount on
declare @backuppath nvarchar(500)
declare @cmd nvarchar(3000)
declare @currenttime datetime
declare @extime int
set @currenttime=GETDATE()
set  @extime=DATEPART(MI,@currenttime)
set @backuppath = 'S:\backup\old\suzhou'
-- 4.获得文件列表
set @cmd = 'dir /b /s ' + @backuppath
truncate table [fileList-suzhou]
insert into [fileList-suzhou]
exec master.sys.xp_cmdshell @cmd
declare @lastbackup nvarchar(500)
select @lastbackup=max(backupfile)
from [fileList-suzhou]
where backupfile like '%_log_%.trn'

if exists(select backupfile from [pre-suzhou] where backupfile=@lastbackup)
  begin
   --print '日志备份文件'+@lastbackup+'是过期的日志备份';
   print 0;
   return;
  end
else
  begin
   insert into [pre-suzhou](backupfile) values(@lastbackup)
   set @cmd = 'restore log suzhou from disk = '''+ @lastbackup + ''' with norecovery'
   insert into [restorehistoty-suzhou](backupfile) values(@cmd)
   print @cmd
  end

set nocount off
go

3.4 closeshell.txt

--禁用xp_cmdshell，

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options',1
GO
-- To update the currently configured value for advanced options.
RECONfigURE WITH OVERRIDE
GO
-- To disable the feature.
EXEC sp_configure 'xp_cmdshell',0
GO
-- To update the currently configured value for this feature.
RECONfigURE WITH OVERRIDE
GO

-- To disallow advanced options to be changed.
EXEC sp_configure 'show advanced options',0
GO
-- To update the currently configured value for advanced options.
RECONfigURE WITH OVERRIDE
GO

3.5 suzhou.bat 主文件

sqlcmd -S dellsql  -i S:\dba\restore\suzhou\openshell.txt -o S:\dba\restore\suzhou\outopenshell.txt

sqlcmd -S dellsql  -i S:\dba\restore\suzhou\in.txt -o S:\dba\restore\suzhou\out.txt

@echo off
echo >S:\dba\restore\suzhou\tmp.txt

for /f %%a in (S:\dba\restore\suzhou\out.txt) do  set var=%%a

if "%var%" =="0"  goto exist

goto continu 

 

:exist
echo 不需要恢复日志!>>S:\dba\restore\suzhou\tmp.txt
exit

 

 

:continu
echo 恢复日志,继续执行!>>S:\dba\restore\suzhou\tmp.txt

 

sqlcmd -S dellsql  -i S:\dba\restore\suzhou\out.txt   -e  -o  S:\dba\restore\suzhou\re.txt

sqlcmd -S dellsql  -i S:\dba\restore\suzhou\input.txt   -o  S:\dba\restore\suzhou\inputre.txt

sqlcmd -S dellsql -i S:\dba\restore\suzhou\closeshell.txt   -o S:\dba\restore\suzhou\outcloseshell.txt

 

 

4.通过后端计划任务来调用批处理即可

这个就简单了！ 根据自己需要间隔来执行.

 

5.查看执行结果

select top 5 * from master.dbo.[pre-suzhou]
order by ID desc
go

select top 5  * from [restorehistoty-suzhou]
order by ID desc

select top 5 * from [restorehistoty-suzhou] where backupfile in ( select distinct tsql from [restorehistoty-suzhou-tsql] where tsql like '%s:%.trn%' ) order by ID desc

@H_301_4@ @H_301_4@ @H_301_4@ 总结

以上是内存溢出为你收集整理的实战:sqlserver 2008 R2容灾方案:另一种思路-2全部内容，希望文章能够帮你解决实战:sqlserver 2008 R2容灾方案:另一种思路-2所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。