Snapshot Dynamicly

/**********************************************************************************//*Script to create snapshots Dynamicly*//*F.Racionero 09/28/2010*//*f.racionero@hotmail.com*//*Valid to SQL 2005/2008/2008R2*//**********************************************************************************/declare @dbname sysname            --Source database name to snapshot
declare @snap sysname            --Sanpshot Name to create
declare @snap2drop sysname        --Sanpshot to drop
declare @command varchar(2000) --Command to execute during the operations
declare @time varchar(20)        --Time to compose the snapshot name @snap
declare @filename sysname        --Logical file name of source database to asign the new logical name os the sanp
declare @path sysname            --Path where will be created the file
declare @snapNum varchar(1)        --Suffix to create the snapshot file
--Set variables
set nocount on
set @dbname='EducamosMod'
set @time=convert(varchar(16),GETDATE(),112)
set @snap=@dbname + '_snap_' + @time + '_'
select @filename=name from sys.master_files where database_id=DB_ID(@dbname) and file_id=1
SELECT @path=SUBSTRING(physical_name, 1, CHARINDEX(@dbname , LOWER(physical_name)) - 1)
 FROM master.sys.master_files
 WHERE database_id = DB_ID(@dbname) AND file_id = 1
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name like @snap + '%')
    begin
        set @snapNum= +'1'
    end
else
    begin
        select @snapNum=max(right(name,1))+1 from sys.databases where source_database_id =DB_ID(@dbname)
        if @snapNum>3
            set @snapNum='1'
    end
-- Final value of new snapshot database
set @snap+=@snapNum 

print '***************************************************************************************'
--Drop old sanapshot
if (select COUNT(*) from sys.databases where source_database_id=db_id(@dbname) )>=2
begin
select top 1 @snap2drop= DB_NAME(database_id) from sys.databases where source_database_id =db_id(@dbname) order by create_date
begin try
set @command= ('drop database ' + @snap2drop )
exec (@command)
print 'Old Snapshot ' + upper(@snap2drop) + ' of database ' + upper(@dbname) + ' was removed at: ' + convert(varchar(20),getdate(),113)

end try
begin catch
    goto error
end catch
end
--Create command
set @command=
'CREATE DATABASE ' + @snap + '
    ON (NAME = ' + @filename + ',
        FILENAME = ''' + @path + ''+ @snap + '.snap'') 
AS SNAPSHOT OF ' + @dbname
-- Snapshot Command execution

begin try
exec (@command)
print 'New Snapshot ' + upper(@snap) + ' of database ' + upper(@dbname) + ' was created at: ' + convert(varchar(20),getdate(),113)
end try
begin catch
    goto error    
end catch
print '***************************************************************************************'
error:
IF @@ERROR <> 0 
    SELECT 
 ERROR_NUMBER() AS ErrorNumber,
 ERROR_SEVERITY() AS ErrorSeverity,
 ERROR_STATE() as ErrorState,
 ERROR_PROCEDURE() as ErrorProcedure,
 ERROR_LINE() as ErrorLine,
 ERROR_MESSAGE() as ErrorMessage;
set nocount off