September 29, 2010 at 2:00 am
Comments posted to this topic are about the item Snapshot Dynamicly
Francisco Racionero
twitter: @fracionero
November 11, 2010 at 3:08 am
I 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.
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
May 18, 2016 at 7:18 am
Thanks for the script.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply