Technical Article

Snapshot Dynamicly

,

As a part of my security strategy I have created this script to automate the database snapshot creation.

The main idea is to have two snapshot databases always availables to restore some part of databases.

In the script you can see how is named the snapshot, usinf the current date and a secuencial number to identify each one.

After that you will see I remove the oldest file and create a new one.

 

 

 

/**********************************************************************************//*Script to create snapshots Dynamicly*//*F.Racionero 09/28/2010*//*f.racionero@hotmail.com*//*Valid to SQL 2005/2008/2008R2*//**********************************************************************************/declare @dbname sysname            --Source database name to snapshot
declare @snap sysname            --Sanpshot 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
--Set variables
set nocount on
set @dbname='EducamosMod'
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 + '%')
    begin
        set @snapNum= +'1'
    end
else
    begin
        select @snapNum=max(right(name,1))+1 from sys.databases where source_database_id =DB_ID(@dbname)
        if @snapNum>3
            set @snapNum='1'
    end
-- Final value of new snapshot database
set @snap+=@snapNum 

print '***************************************************************************************'
--Drop old sanapshot
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

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating