sp_Snapshot is a procedure that we’ve written to quickly and easily create a database snapshot of one or multiple databases.

Prerequisites

sp_Snapshot uses STRING_SPLIT, if you’re running on a version of SQL Server prior to 2016 or have databases with a compatibility level under 130 you will need to install fn_SplitString

The procedure takes two parameters,

@DatabaseList – a comma delimited string of database names, allows wildcards

@ListOnly – 1- a list of affected databases will be displayed but snapshots aren’t created.

0- Snapshots are created automatically DEFAULT

Example

Create a snapshot of AdventureWorks and all databases with names beginning with ‘SQL’

sp_Snapshot @DatabaseList = 'AdventureWorks,SQL%'

sp_Snapshot

--Author: David Fowler
--Date: 19 September 2017
--(c) SQLUndercover 2017
--sqlundercover.com
USE master
GO
CREATE PROCEDURE sp_Snapshot
(@DatabaseList NVARCHAR(4000),
@ListOnly BIT = 0)
AS 
BEGIN
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList
CREATE TABLE #DatabaseList (name NVARCHAR(4000))
IF OBJECT_ID('tempdb..#DatabasesFinal') IS NOT NULL
DROP TABLE #DatabasesFinal
--set compatibility mode
DECLARE @compatibility BIT
--set compatibility to 1 if server version includes STRING_SPLIT
SELECT@compatibility = CASE
WHEN SERVERPROPERTY ('productversion') >= '13.0.4001.0' AND Compatibility_Level >= 130 THEN 1
ELSE 0
END
FROM sys.databases
WHERE name = DB_NAME()
--select the database list into a temp table so that we can work with it
IF @compatibility = 1 --if compatibility = 1 then use STRING_SPLIT otherwise use fn_SplitString
INSERT INTO #DatabaseList
SELECT value
FROM STRING_SPLIT(@DatabaseList,',')
ELSE
INSERT INTO #DatabaseList
SELECT StringElement AS name
FROM master..fn_SplitString(@DatabaseList,',')
--get list of databases, including those covered by any wildcards
SELECT QUOTENAME(name) AS name
INTO #DatabasesFinal
FROM sys.databases databases
WHERE EXISTS
(SELECT name
FROM #DatabaseList
WHERE databases.name LIKE #DatabaseList.name)
IF @ListOnly = 1 --if @listonly set then only print the affected databases
SELECT name
FROM #DatabasesFinal
ELSE
BEGIN
DECLARE @Databases VARCHAR(128)
------------------------------------------------------------------------------------------------------
--Loop through each database creating snapshots
DECLARE databases_curr CURSOR
FOR SELECT name
FROM #DatabasesFinal
OPEN databases_curr
FETCH NEXT FROM databases_curr
INTO @Databases
WHILE @@FETCH_STATUS = 0
BEGIN
--create snapshots
EXEC ('USE ' + @Databases +
'DECLARE @DatabaseName VARCHAR(128)
DECLARE @SnapshotName VARCHAR(128)
SET @DatabaseName = DB_NAME()
SET @SnapshotName = DB_NAME() + ''_snapshot'' 
--table variable to hold file list
DECLARE @DatabaseFiles TABLE (id INT identity(1,1),name VARCHAR(128), physical_name VARCHAR(400)) 
--populate table variable with file information
INSERT INTO @DatabaseFiles (name, physical_name)
SELECT name, physical_name
FROM sys.database_files
WHERE type != 1 
--begin building snapshot script
DECLARE @SnapshotScript VARCHAR(1000)
SET @SnapshotScript = ''CREATE DATABASE '' + QUOTENAME(@SnapshotName) + '' ON '' 
--loop through datafile table variable
DECLARE @LoopCounter INT = 0 
DECLARE @FileCount INT
SELECT @FileCount = COUNT(*)
FROM @DatabaseFiles 
WHILE @LoopCounter < @FileCount
BEGIN
SET @LoopCounter = @LoopCounter + 1
SELECT @SnapshotScript = @SnapshotScript + ''(NAME = '' + QUOTENAME(name) + '', FILENAME = '''''' + physical_name + ''.ss''''),''
FROM @DatabaseFiles
WHERE id = @LoopCounter
END 
--loop will have added an unwanted comma at the end of the script, delete this comma
SET @SnapshotScript = LEFT(@snapshotscript, LEN(@snapshotscript) -1) 
--add AS SNAPSHOT to script
SET @SnapshotScript = @SnapshotScript + '' AS SNAPSHOT OF ['' + @DatabaseName + '']'' 
--Generate the snapshot
PRINT ''Creating Snapshot for ' + @Databases + '''
EXEC (@SnapshotScript)')
FETCH NEXT FROM databases_curr
INTO @Databases
END
CLOSE databases_curr
DEALLOCATE databases_curr
END
END

I hope you find this useful and thanks for reading