Snapshot Dynamicly

  • Comments posted to this topic are about the item Snapshot Dynamicly

    Francisco Racionero
    twitter: @fracionero

  • 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

  • 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