SQL Server中使用資料庫快照集的方式來完成測試環境中資料庫的輕量級備份還原作業

標籤：dba   檔案   資料還原   暫存資料表   begin   sel   迴圈   taint   適應   

原文:SQL Server中使用資料庫快照集的方式來完成測試環境中資料庫的輕量級備份還原作業

 

在開發或者測試環境的資料庫中，經常會發現有開發或者測試人員誤刪除表或者資料的情況，
對於開發或者測試庫，一般都沒有安排定時的備份任務去備份資料庫，
一方面是由於儲存資源有限，不太可能給開發或者測試環境準備大量的儲存空間，
二是必要性不是很強，開發或者測試庫的資料庫物件變化太多，通過還原備份的方式又有可能衝掉其最近建立的資料庫物件。
但是不得不面對的問題就是個別人在執行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 status = 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迴圈暫存資料表的方式，也可以達到遊標迴圈的效果，並且這種文法邏輯結構上更清晰簡便，根本不會忘記，呵呵。

 

SQL Server中使用資料庫快照集的方式來完成測試環境中資料庫的輕量級備份還原作業