Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Snapshot Dynamicly Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 2:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 24, 2014 10:24 AM
Points: 263, Visits: 342
Comments posted to this topic are about the item Snapshot Dynamicly

Francisco Racionero
twitter: @fracionero
Post #994974
Posted Thursday, November 11, 2010 3:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:38 AM
Points: 1,287, Visits: 777
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




Post #1019198
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse