2010-11-09

Create a database snapshot

I've been meaning to write this one for a while. The need arose today to be able to create a database snapshot fairly quickly. Here's what I came up with:

set nocount on
go
declare @timestamp varchar(23) = datediff(second, '1970-01-01', getdate())
declare @file_suffix varchar(50) = '_' + @timestamp + '.snapshot'
declare @database sysname = 'p28TCS1'
declare @sql varchar(max)

select @sql = 'create database ' + quotename(@database + '_' + @timestamp) + ' on '

select @sql += coalesce(stuff((select 
    ',(NAME = ''' + name + ''', FILENAME = ''' + physical_name + @file_suffix + ''')'
    from sys.master_files
where database_id = db_id(@database)
    and type_desc <> 'LOG'
    for xml path ('')), 1, 1, ''), '')

select @sql += ' as snapshot of ' + quotename(@database)
    
exec( @sql )

select 'Snapshot ' + quotename(@database + '_' + @timestamp) + ' of ' + quotename(@database) + ' created'

And remember, you got what you paid for.

No comments:

Post a Comment