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,1) NOT NULL,
[backupfile] [nvarchar](255) not null primary key,
[resdate] [datetime] NULL,
)
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
分享到:
相关推荐
新形势下的DELL EMC数据容灾保护解决方案-v1 -- Dell.pdf
新形势下的DELL EMC数据容灾保护解决方案-v1 -- Dell.zip
容灾-异地容灾备份-容灾一体机-数据安全备份-数据库双活网关.docx
sqlserver 2008双机热备,最好有三台服务器,其中一台当做见证服务器,没有也没有关系,该文档从实施到最后测试均有详细的记载,其中实施过程中遇到的问题也在网上得到了解答,感谢互联网上的大神博客。如有问题可以...
方案-惠普容灾与备份解决方案介绍 方案-金融灾备数据中心技术方案 方案-某电信容灾方案 方案-容灾解决方案 方案-容灾需求分析及方案 方案-软件定义的容灾解决方案 方案-数据容灾备份解决方案 方案-智慧容灾 课件-...
容灾备份:成就兴隆大家庭“60小时不夜城”
NetApp 容灾备份技术(SnapMirror-SnapVault-OSSV).pdf
这个是介绍本地的集群方案,有很多集群容灾方案方面参考价值
SQL数据库负载均衡及容灾方案PDF
数据中心网络存储容灾技术白皮书----内部文档
Oracle容灾备份: 实时备份、 数据迁移应用实践 内容: DDS实时复制软件的技术体系 DRS逻辑备份与恢复 DDS解决方案与案例 DDS产品特点 技术交流
两地三中心不仅是容灾/双活,更是 对基础架构的持续优化
云上跨Region容灾-异地容灾解决方案 云上两地三中心容灾解决方案 D2C备份,备份软件部署在云中 D2C备份,备份软件部署在用户数据中心 D2D2C备份,本地备份再分级存储至云 二维码支付业务跨云双活容灾 Region内跨AZ...
通过与XX公司的技术人员的探讨和交流沟通,针对用户在日常维护过程中所碰到的一些迫切需要解决的问题,通过全面、深入的测试来检验爱数备份平台是否能具有满足各种实际需求的功能和特性,为今后数据保护与恢复管理的...
建立容灾系统的必要性 容灾介绍 XX电信现有系统 容灾解决方案 容灾趋势及国内外案例介绍 问与答
容灾-备份-数据恢复-异地容灾.pdf
本文将通过介绍一些业界主流的数据库高可用架构、每种方案的特性和优缺点,以及数据库高可用架构的自动化运维实现,讲讲数据库高可用容灾方案设计与实现,希望抛砖引玉,和大家一起讨论。
MySQLreplicate容灾方案.docx
Oracle数据库异地容灾方案.doc
HDS 容灾方案,orace数据库复制技术。。