Quick snapshot

/***************************************************************************
Snapshot script:
The script enables a quick creation of snapshots for databases. 
It has two run modes defined by the optional parameter @exec_mode.
  @exec_mode=0 (default) means printing the create snapshot statement and
  @exec_mode=1 means creation of the snapshot database.
  @database is mandatory input parameter.
Tested on SQL Server 2008 and 2012.

Usage:
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012'
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012', @exec_mode=0
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'CWNET', @exec_mode=1

Date created:2014-Mar-12
Author:Igor Micev (igor.micev@hotmail.com; igor.micev@outlook.com)
***************************************************************************/
USE [master]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Create_UserDb_Snapshot]') AND [type] in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Create_UserDb_Snapshot]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[Create_UserDb_Snapshot]
@database varchar(50),
@exec_mode bit=0
as
begin
 set nocount on
 declare @err_text nvarchar(200)
 if @database in ('master','model','tempdb')
 begin
set @err_text = 'Snapshot is not allowed for master, model and tempdb databases.'
RAISERROR(@err_text,10,1)
return;
 end
 if object_id('tempdb..#usr_db_files_tbl') is not null
    drop table #usr_db_files_tbl
 create table #usr_db_files_tbl(
  [logicalName] varchar(100),
  [physicalName] varchar(200)
  )
 insert into #usr_db_files_tbl(logicalName,physicalName)
 select [mf].[name],[mf].[physical_name]
 from sys.master_files as [mf]
 where [mf].[database_id]=db_id(@database) and [mf].[type]=0 and [mf].[name] not in ('master','model','tempdb')

 if (select count(*) from #usr_db_files_tbl)=0
 begin
set @err_text = 'The specified database does not exist.' 
RAISERROR(@err_text,10,1) 
return;
 end
 
 declare @dyn_sql nvarchar(max)
 declare @extension varchar(20)='snap'+right(replace(replace(replace(convert(varchar(20),getdate(),113),'-',''),':',''),' ',''),4)
 declare @curLogicalName varchar(100), @curPhysicalName varchar(200)
 
 declare snap_cursor cursor for
 select [t].[logicalName], [t].[physicalName] from #usr_db_files_tbl as [t]
 open snap_cursor

 declare @snapDbName nvarchar(100)
 set @snapDbName=@database+'_snapshot_'+replace(replace(replace(convert(varchar(20),getdate(),113),'-',''),':',''),' ','')

 fetch next from snap_cursor into @curLogicalName,@curPhysicalName
 set @dyn_sql='CREATE DATABASE '+@snapDbName+' ON '+char(10)
 while (@@FETCH_STATUS=0)
 begin
set @dyn_sql+='(name='''+@curLogicalName+''', filename='''+replace(replace(@curPhysicalName,right(@curPhysicalName,4),'.'+@extension),right(@curPhysicalName,4),'.'+@extension)+''')' 
fetch next from snap_cursor into @curLogicalName,@curPhysicalName
if (@@FETCH_STATUS=0)
 set @dyn_sql+=', '+char(10)
 end

 set @dyn_sql+=char(10)
 set @dyn_sql+='AS SNAPSHOT OF '+@database+char(10)
 
 if @exec_mode=convert(bit,0)
  print char(10)+@dyn_sql
 
 if @exec_mode=convert(bit,1)
 begin
  begin try
   exec sp_executesql @dyn_sql
   print 'Snapshot database: '+@snapDbName+' created successfully.'
  end try
  begin catch
   set @err_text = 'Snapshot for database '+@database+' cannot be created.'
   RAISERROR(@err_text,16,1) 
  end catch
 end
 
 close snap_cursor
 deallocate snap_cursor
 drop table #usr_db_files_tbl
 WAITFOR DELAY '00:00:01';
 return;

end