|
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:backupoldsuzhou'
?-- 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:dbarestoresuzhouopenshell.txt -o S:dbarestoresuzhououtopenshell.txt
sqlcmd -S dellsql? -i S:dbarestoresuzhouin.txt -o S:dbarestoresuzhouout.txt
?@echo off
?echo >S:dbarestoresuzhoutmp.txt
?for /f %%a in (S:dbarestoresuzhouout.txt) do? set var=%%a
if "%var%" =="0"? goto exist
goto continu?
?
?:exist
?echo 不需要恢复日志!>>S:dbarestoresuzhoutmp.txt
?exit
?
?
:continu
?echo 恢复日志,继续执行!>>S:dbarestoresuzhoutmp.txt
?
sqlcmd -S dellsql? -i S:dbarestoresuzhouout.txt?? -e? -o? S:dbarestoresuzhoure.txt
?sqlcmd -S dellsql? -i S:dbarestoresuzhouinput.txt?? -o? S:dbarestoresuzhouinputre.txt
sqlcmd -S dellsql -i S:dbarestoresuzhoucloseshell.txt?? -o S:dbarestoresuzhououtcloseshell.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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
