InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:57:59 GMT20http://www.sqlservercentral.com/Forums/Topic994974-1030-1.aspxI had trouble executing the original script. This one worked for me:You only have to adjust the variable @dbname.Snapshot will be created in the same folder as the original database.[code="plain"]declare @dbname sysname --Source database name to snapshotdeclare @snap sysname --Snapshot Name to createdeclare @snap2drop sysname --Sanpshot to dropdeclare @command varchar(2000) --Command to execute during the operationsdeclare @time varchar(20) --Time to compose the snapshot name @snapdeclare @filename sysname --Logical file name of source database to asign the new logical name os the sanpdeclare @path sysname --Path where will be created the filedeclare @snapNum varchar(1) --Suffix to create the snapshot filedeclare @snapMinNum varchar(1) ----Set variablesset nocount onset @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=1SELECT @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 = 1IF 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&gt;3 and @snapMinNum = 1 set @snapNum='2' if @snapNum&gt;3 and @snapMinNum = 2 set @snapNum='1' end-- Final value of new snapshot databaseset @snap=@snap + @snapNumprint '***************************************************************************************'--Drop old snapshotif (select COUNT(*) from sys.databases where source_database_id=db_id(@dbname) )&gt;=2begin select top 1 @snap2drop= DB_NAME(database_id) from sys.databases where source_database_id =db_id(@dbname) order by create_datebegin 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 trybegin catch goto errorend catchend--Create commandset @command='CREATE DATABASE ' + @snap + ' ON (NAME = ' + @filename + ', FILENAME = ''' + @path + ''+ @snap + '.snap'') AS SNAPSHOT OF ' + @dbname-- Snapshot Command executionbegin tryexec (@command)print 'New Snapshot ' + upper(@snap) + ' of database ' + upper(@dbname) + ' was created at: ' + convert(varchar(20),getdate(),113)end trybegin catch goto error end catchprint '***************************************************************************************'error:IF @@ERROR &lt;&gt; 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 [/code]Thu, 11 Nov 2010 03:08:15 GMTRobbert Hofhttp://www.sqlservercentral.com/Forums/Topic994974-1030-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Sanpshot/71277/">Snapshot Dynamicly</A>[/B]Wed, 29 Sep 2010 02:00:21 GMT@fracionero