Nueva versión - Crear Database Snapshot dinámicamente

Acá les adjunto la versión mas reciente del SP para crear Snapshots de base de datos de forma dinámica.
Permite la creación de multiples snapshots para una misma BD y la posibilidad de incluir el path en donde almacenar dichos Snapshots.


CREATE PROCEDURE usp_CreateSnapshot (@SourceDB varchar(200),@DestDB varchar(200)=NULL, @Message VARCHAR(200) =NULL OUTPUT)
AS 
--Created by Adrian Miranda Cordero
--Create a snapshot dinamically 
--Following script is intented to solve the problem of creating a database snapshot from the scratch by providing two or three parameters-

DECLARE @DatabaseName as varchar(200)
DECLARE @DestDBName as varchar(200)
DECLARE @SQLQuery as Nvarchar(MAX)
DECLARE @UseSQL as varchar (205)
DECLARE @Counter as smallint
DECLARE @FileId as smallint 
DECLARE @FileName as nvarchar(max)
DECLARE @LogicalName as varchar(200)
DECLARE @Files as varchar(max)
DECLARE @Inc_File int --Consecutive for a file name when you want multiple snapshots for the same DB

SET @DatabaseName =@SourceDB
IF @DestDB IS NULL 
SET @DestDBName= @SourceDB
ELSE
SET @DestDBName=@DestDB
SET @Inc_File = 0
SET @SQLQuery =''

IF (SELECT COUNT(1) FROM sys.databases WHERE name  in ('master', 'msdb',',model','tempdb') and  name = @Databasename) > 0
SET @Message='Can not create a database snapshot based on a system database'
ELSE IF (SELECT NAME FROM sys.databases WHERE NAME = @DatabaseName) IS NULL
SET @Message= 'Database not found.'
ELSE
BEGIN
SELECT @Inc_File=COUNT(1) FROM sys.databases WHERE source_database_id=DB_ID(@DatabaseName)
SET @Inc_File = @Inc_File + 1
DECLARE @DBFiles TABLE (
ID smallint Identity(1,1),
FileId int,
Name varchar (max),
FilePath varchar (max)
)
SET @SQLQuery = 'SELECT FILE_ID,name,Physical_Name FROM sys.database_files WHERE type_desc <>''''LOG'''''; 
SET @UseSQL = 'USE ' + @DatabaseName + '; EXEC sp_executesql N''' + @SQLQuery + '''';
INSERT INTO @DBFiles Exec (@UseSQL)
SET @SQLQuery = 'CREATE DATABASE ' + ISNULL(@DestDBName,@DatabaseName) + '_Snap' + LTRIM(STR(@Inc_File)) +  ' ON '
SELECT @Counter=count(1) FROM @DBFiles
SET @FileId =1
SET @Files = ''
WHILE @FileId <= @Counter
BEGIN
SELECT @LogicalName = Name, @FileName = FilePath FROM @DBFiles where ID=@Fileid
print @FileName
SET @Files = @Files + '(Name =' + @LogicalName + ',' + 'FileName = ' + '''' + LEFT(@FileName,CHARINDEX('.',@FileName) - 1)  

+  LTRIM(STR(@Inc_File)) +  '.ss'')'
IF @FileId <> @Counter
SET @Files=@Files + ','
SET @FileId = @FileId + 1 
END
SET @SQLQuery = @SQLQuery + @Files + ' AS SNAPSHOT OF ' + @DatabaseName
EXEC (@SQLQUERY)
IF @@ERROR =0
SET @Message= 'Database Snapshot created successfully'
END