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