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

--生成全实例下的数据库快照
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 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 catch

end