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