Oct 032011

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
	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 CreateDatabaseSnapshot 'AdventureWorks2008R2_snapshot', 'AdventureWorks2008R2'