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
)
as
begin
    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 catch

end

實話講，已經完全忘記了T-SQL中游標的語法了，通過while迴圈臨時表的方式，也可以達到游標迴圈的效果，並且這種語法邏輯結構上更清晰簡便，根本不會忘記，呵呵。