Snapshot Dynamicly

• @fracionero

  Right there with Babe

  Points: 737

  More actions

  September 29, 2010 at 2:00 am

  #228852

  Comments posted to this topic are about the item Snapshot Dynamicly

  Francisco Racionero
  twitter: @fracionero

• Robbert Hof

  SSCrazy

  Points: 2778

  More actions

  November 11, 2010 at 3:08 am

  #1249375

  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

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 18, 2016 at 7:18 am

  #1879866

  Thanks for the script.