创建快照数据库

 创建作业时定执行SP:up_GenSnapshotDB，就可以创建快照数据库了

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[up_GenSnapshotDB]    Script Date: 2021-5-6 11:15:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create proc [dbo].[up_GenSnapshotDB](
	@DatabaseList varchar(1000),  --要快照的数据库中列表多个数据库“,”分开如： test,test1
	@FilePath NVARCHAR(1000),    --快照文件路径
	@RetPolicy int                --保留最近几个快照的数据库
)
as

/*
declare 
	@DatabaseList varchar(1000)= 'N205,jWMS',
	@FilePath NVARCHAR(1000)='G:\',
	@RetPolicy int=3

--*/

drop table if exists #dblist
select databasename=value,rid=ROW_NUMBER()over(order by value)
into #dblist
from string_split(@DatabaseList,',')
where value>''

declare @sql nvarchar(4000)='',@i int,@max int,@databasename nvarchar(50)
declare @Time nvarchar(50)

select @i=1,@max=max(rid) from #dblist

while @i<=@max begin
	
	select @sql='',@Time=CONVERT(varchar(50),getdate(),112)+replace(CONVERT(varchar(50),getdate(),108),':','')
	select @databasename=databasename from #dblist where rid=@i

	select @sql=@sql+'(name = '+[name]+', filename = '''+@FilePath+[name]+@Time+''' ),'+char(13)
	from sys.sysaltfiles where dbid=DB_ID(@databasename) and groupid>=1

	set @sql=left(@sql,len(@sql)-2)

	set @sql='
		Create Database '+@databasename+'_'+@Time+'
		ON
			'+@sql+'
		As snapshot of '+@databasename+'
	'
	--print @sql

	exec(@sql)

	--------------------------
	set　@sql =''
	;
	with list as(
		select name,source_database_id,create_date,rid=ROW_NUMBER()over(partition by source_database_id order by create_date desc)
		from sys.databases 
		where source_database_id is not null 
	)
	select @sql=@sql+'drop database '+[name]+';'+char(13)
	from list
	where rid>@RetPolicy

	--print @sql
	exec(@sql)

	set @i=@i+1
	
end

GO