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
