SQL Server中使用数据库快照的方式来完成测试环境中数据库的轻量级备份还原操作

2017-10-26 08:09:57来源:cnblogs.com作者:MSSQL123人点击

分享

在开发或者测试环境的数据库中,经常会发现有开发或者测试人员误删除表或者数据的情况,
对于开发或者测试库,一般都没有安排定时的备份任务去备份数据库,
一方面是由于存储资源有限,不太可能给开发或者测试环境准备大量的存储空间,
二是必要性不是很强,开发或者测试库的数据库对象变化太多,通过还原备份的方式又有可能冲掉其最近新建的数据库对象。
但是不得不面对的问题就是个别人在执行update或者delete操作的时候“忘了加where条件”这种事情的发生。

这是开发或者测试环境的一个痛点,相信多数人都经历过,
当面对开发人员过来说“我刚才delete表的时候忘了加where条件,结果全删了,能不能还原一下?”这个问题的时候,
实则有点尴尬,只能告知无法还原,sqlserver不支持闪回功能,又因为测试数据库没有备份,确实无能为力。
此时,开发人员往往会报以鄙视+无奈的眼神,作为冒牌DBA,确实难以相助。
因为测试环境的数据库没有备份操作,即便是有备份,还原了备份可能会冲掉备份之后新建的数据库对象(表,存储过程,视图等等)
如果用备份还原的方式,一是不太适用当前这种情形,二是确实过于重量级了。

为了切实地解决这个问题,同时又能够以最小的代价来实现,可以定时给测试库打快照的这种轻量级的方式来解决误删除数据的问题。
一是快照占用的空间并不大,
二是可以通过轻量级的方式去还原单个表的数据
花了点时间,写了个全示例下用户数据库的生成快照操作,可以安排一个定时任务来完成定时对数据库生成快照的方式来做一个轻量级的备份。
因此就可以方便地解决执行update或者delete操作的时候“忘了加where条件”误删数据或者表的情况。

通过定时对开发环境的数据库打快照,一旦出现误删数据的情况,可以根据最近的快照中的数据还原误删的数据,同时可以根据实际情况,删除创建的已过期的快照。

简单测试了一下,应该可以达到目的,没有做完整的测试,原理很简单,双层while循环,就是通过循环每一个数据库对应的物理文件,动态生成一个创建数据库快照的语句
此种方式仅适应于开发或者测试环境数据库的轻量级备份,不适应于生产环境。

--生成全实例下的数据库快照create  proc [dbo].[CreateSnapshotForBackUp](    --数据库快照文件位置    @p_FilePath varchar(500),    --保留最近N个小时之内创建的快照,单位为小时    @p_RetainTime int)asbegin    set nocount on;    declare @strSql            varchar(2000)    declare @strDatetime    varchar(20)    declare @strDBFileName    varchar(200)    set @strDatetime =  replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')        if object_id('tempdb..#databaseName') is not null    begin         drop table #databaseName    end    select name     into #databaseName      from  sys.databases where database_id>6 and source_database_id is null         declare @databaseName varchar(200);    declare @databaseCnt int;    select @databaseCnt = count(1) from #databaseName;    while @databaseCnt>0    begin        select top 1 @databaseName = name from #databaseName        if object_id('tempdb..#dbFiles') is not null        begin             drop table #dbFiles        end        select concat('(','name = ' , name , ',FileName = ''',@p_FilePath,name,'_',@strDatetime,'.ss'' )' ) as strFileName        into #dbFiles        from sys.sysaltfiles         where  dbid=db_id(@databaseName) and fileid<>2        DECLARE @dbFileCnt int = 0;        SELECT @dbFileCnt = COUNT(1) FROM  #dbFiles        while @dbFileCnt>0        begin            select top 1 @strDBFileName = strFileName from #dbFiles;            set @strSql=CONCAT(@strSql,',',@strDBFileName,char(10));            delete top (1) from #dbFiles;            select @dbFileCnt = count(1) from #dbFiles;        end        set @strSql=stuff(@strSql,1,1,'')        set @strSql = CONCAT('create database ' ,@databaseName,'_',@strDatetime, char(10),                             ' on ' , char(10), @strSql                             ,'as snapshot of ' , @databaseName)        begin try            print @strSql            exec(@strSql)            select @databaseName+'snapshot create successful:'+@databaseName+@strDatetime            set @strSql = ''        end try        begin catch            select 'snapshot create fail'            throw        end catch        delete top (1) from #databaseName        select @databaseCnt = count(1)  from #databaseName    end    --删除过期的数据库快照    begin try        if object_id('tempdb..#snapshotname') is not null            drop table #snapshotname        select name         into #snapshotname         from sys.databases         where source_database_id is not null and create_date<dateadd(hh,-@p_RetainTime,getdate());        declare @cnt int = 0;        declare @strDBName varchar(200) = ''        declare @strDropDatabase varchar(max) =  ''        select @cnt = count(1) from #snapshotname        while @cnt>0        begin            select top 1 @strDBName = name from #snapshotname;            set @strDropDatabase = 'drop database ' +@strDBName;            print @strDropDatabase            exec (@strDropDatabase);            delete top (1) from #snapshotname;            select @cnt = count(1) from #snapshotname        end    end try    begin catch        select N'snapshot delete fail'        throw    end catchend

实话讲,已经完全忘记了T-SQL中游标的语法了,通过while循环临时表的方式,也可以达到游标循环的效果,并且这种语法逻辑结构上更清晰简便,根本不会忘记,呵呵。

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台