Create Database Snapshot Dynamically
Dynamic SQL can be used to generate a database snapshot. Useful for a database with a number of files.
2018-08-23 (first published: 2016-11-04)
3,406 reads
/*************************************************************************** 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