Script para crear Database Snapshots Dinámicos

Conforme han ido pasando las versiones de SQL Server notamos que aun no existe una interfaz gráfica que permita la creación de Database Snapshots, el código es sencillo si se cuenta con únicamente un datafile, el problema se hace mas grande cuando estas bases de datos tienen mas de un datafile. Pensando en estas situaciones es que decidí crear el siguiente SP que permite la creación dinámica de un snapshot de base de datos. Esta es la primer versión del SP, el cual estará sujeto a cambios o mejoras. Cualquier sugerencia es bien recibida.

Saludos.

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

--Code can be changed ad needed just remember to say thank you every time you rise a prayer XD
  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)

SET @DatabaseName =@SourceDB

IF @DestDB IS NULL

SET @DestDBName= @SourceDB




ELSE
  SET @DestDBName=@DestDB

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
   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 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)) + '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