Database snapshot is a great SQL Server built-in feature serving many situations for programmers and DBAs to quickly create a read only copy of a operational database. Sometime, creating a database snapshot is a daunting work especially when you have hundreds database files. Here I give you the code to generate database snapshot automatically with few parameters regardless number of files the database has.
create procedure CreateDatabaseSnapshot
(
@SnapshotName sysname,
@SourceDatabaseName sysname = null
)
as
begin
set xact_abort on
select @SourceDatabaseName = ISNULL(@SourceDatabaseName, db_name())
declare @SQL nvarchar(max), @Proc sysname = quotename(@SourceDatabaseName)+'..sp_executesql'
declare @t table(name sysname, physical_name sysname, i int identity(1,1))
insert into @t
exec @Proc N'select name, physical_name from sys.database_files where type = 0'
select @SQL = 'create database '+ QUOTENAME(@SnapshotName) + ' on '
select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+'''),'
from @t
where i>1
select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+''')'
from @t
where i=1
select @SQL = @SQL + ' AS SNAPSHOT OF '+QUOTENAME(@SourceDatabaseName)+' ;'
exec(@SQL)
end
go
Usage:
exec CreateDatabaseSnapshot 'AdventureWorks2008R2_snapshot', 'AdventureWorks2008R2'
