declare @dbname sysname            --Source database name to snapshot
declare @snap sysname              --Snapshot 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
declare @snapMinNum varchar(1)     --
--Set variables
set nocount on
set @dbname='snap_test'
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 + '%')
        set @snapNum= '1'
else
    begin
        select @snapNum=max(right(name,1))+1 from sys.databases where source_database_id =DB_ID(@dbname)
        select @snapMinNum=min(right(name,1)) from sys.databases where source_database_id =DB_ID(@dbname)
        if @snapNum>3 and @snapMinNum = 1
              set @snapNum='2'
        if @snapNum>3 and @snapMinNum = 2
              set @snapNum='1'
    end
-- Final value of new snapshot database
set @snap=@snap + @snapNum
print '***************************************************************************************'
--Drop old snapshot
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