USE master;

SET NOCOUNT ON;

GO

DECLARE @dbname varchar(255); --数据库名

DECLARE @snapname varchar(255);--快照名

DECLARE @snap_dir varchar(255);--保存路径

SET @dbname='DB1';

SET @snapname='DB1_SNAP';

SET @snap_dir='D:\DB\'

BEGIN TRY

-- 删除快照如果快照存在

IF  EXISTS (SELECT name FROM master.sys.databases

WHERE name = @snapname)

BEGIN

   EXEC ('DROP DATABASE '+@snapname);

END

--将数据库文件放入临时表中

SELECT name AS DBFileName INTO #tempFiles

FROM master.sys.master_files

WHERE type<>1 AND database_id=DB_ID(@dbname);

DECLARE @tempSQL NVARCHAR(2000);

DECLARE @tempFileName NVARCHAR(200)

SET @tempSQL='CREATE  DATABASE '+@snapname+' ON ';

WHILE((SELECT COUNT(1) FROM #tempFiles)>0)

BEGIN

   SELECT TOP(1) @tempFileName=DBFileName FROM #tempFiles;

   SET @tempSQL=@tempSQL+'(NAME='+''''+@tempFileName+''',FILENAME='''+@snap_dir+@tempFileName+'.snap'')';

   DELETE FROM #tempFiles WHERE DBFileName=@tempFileName;

END

DROP TABLE #tempFiles;

SET @tempSQL=@tempSQL+'AS SNAPSHOT OF '+@dbname+';'

PRINT '创建脚本：'

PRINT @tempSQL;

EXEC(@tempSQL)

PRINT '创建成功';

END TRY

BEGIN CATCH

PRINT '执行出错，错误信息：'

PRINT ERROR_MESSAGE()

END CATCH