Create SQL Server Database Snapshot Dynamically

Create a SQL Server database snapshot dynamically without having to write the file names and paths.

This script generates a snapshot of your database with two variable inputs; database name, and snapshot name appended.

The snapshot will be named @SourceDatabase + ‘_’ + @SnapshotAppend. So for example ‘DemoDb_Snap_20180810.ss’.

You can also override the file path using @FilePath.

Use the @Debug variable to print the output first for review.

The script will error if the database or the DATA file path doesn’t exist.

Tested on SQL Server 2012

Original post.

/**********************
* Title:	Create Database Snapshot
* Date:		2016-11-01
* Author:	Joe McDermott
* Link:		https://msdn.microsoft.com/en-us/library/ms175876(v=sql.110).aspx
* Notes:	This script will generate SQL based on the source database and tagert appended name in order to generate a database snapshot.
*			Please read comments at the end of variables. Debug is on by default.
* Amend:	2016-11-17, updated with dmv SQL and help given from Budd on SQL Server Central. Changed file path to be taken from sys.master_files.
***********************/

DECLARE  @SourceDatabase	varchar(128)	= 'MyTestDb'		-- Name of the database you want to snapshot from.
		,@SnapshotAppend	varchar(128)	= 'Snap_20161001'	-- Add here what you want to append to the database name for the snapshot. (Example: Snap_20161001)
		,@FilePath			varchar(200)	= NULL				-- Edit if you want the snapshot to reside somewhere else. (Example: 'C:\Override\Path\')
		,@FileSql			varchar(3000)	= ' -- Leave blank.
		,@SnapSql			nvarchar(4000)
		,@Debug				bit				= 0

IF DB_ID(@SourceDatabase) IS NULL
	RAISERROR('Database doesn''t exist. Please check spelling and instance you are connected to.',1,1)

--==================================
-- 1) Set the file path location of the snapshot data files.
--==================================

IF @FilePath = ''
	SET @FilePath = NULL

--==================================
-- 2) Dynamicly build up a list of files for the database to snapshot.
--==================================

SELECT @FileSql = @FileSql +
	CASE -- Case statement used to wrap a comma in the right place.
		WHEN @FileSql  ''
		THEN + ','
		ELSE ''
	END + '
		( NAME = ' + mf.name + ', FILENAME = ''' + ISNULL(@FilePath, LEFT(mf.physical_name,LEN(mf.physical_name)- 4 ) ) + '_' + @SnapshotAppend + '.ss'')'
		-- Remove file extension .mdf, .ndf, and add .ss
FROM sys.master_files AS mf
	INNER JOIN sys.databases AS db ON db.database_id = mf.database_id
WHERE db.state = 0 -- Only include database online.
AND mf.type = 0 -- Only include data files.
AND db.[name] = @SourceDatabase

--==================================
-- 3) Build the create snapshot syntax.
--==================================
SET @SnapSql =
'
CREATE DATABASE ' + @SourceDatabase + '_' + @SnapshotAppend + '
    ON '
	+ @FileSql +
	'
    AS SNAPSHOT OF '+ @SourceDatabase + ';'

--==================================
-- 4) Print or execute the dynamic sql.
--==================================
IF (@Debug = 1)
	BEGIN
		PRINT @SnapSql
	END
ELSE
	BEGIN
		EXEC sp_executesql @stmt = @SnapSql
	END
GO