sp_Snapshot.sql

/******************************************************************

Author: David Fowler
Revision date: 26 January 2024
Version: 2

© www.sqlundercover.com 


This script is for personal, educational, and internal 
corporate purposes, provided that this header is preserved. Redistribution or sale 
of this script,in whole or in part, is prohibited without the author's express 
written consent. 

The software is provided "as is", without warranty of any kind, express or
implied, including but not limited to the warranties of merchantability,
fitness for a particular purpose and noninfringement. in no event shall the
authors or copyright holders be liable for any claim, damages or other
liability, whether in an action of contract, tort or otherwise, arising from,
out of or in connection with the software or the use or other dealings in the
software.

******************************************************************/

CREATE OR ALTER PROC sp_snapshot (
	@DatabaseList NVARCHAR(4000)
	,@Suffix NVARCHAR(100) = 'snapshot'
	,@FilePath NVARCHAR(255) = ''
	,@Timestamp BIT = 0
	,@DateFormat INT = 126
	,@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

	IF @Timestamp = 1
	BEGIN
		SET @Suffix += '_' + CONVERT(VARCHAR, GETDATE(), @DateFormat)
	END

	--we need to strip out certain characters from the file name that tend to occur in dates6
	SET @Suffix = REPLACE(@Suffix, ':', '')
	SET @Suffix = REPLACE(@Suffix, '\', '')
	SET @Suffix = REPLACE(@Suffix, '/', '')

	--select the database list into a temp table so that we can work with it
	INSERT INTO #DatabaseList
	SELECT value
	FROM STRING_SPLIT(@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
			DECLARE @SQL VARCHAR(MAX)

			SET @SQL = 'USE ' + @Databases + 'DECLARE @DatabaseName VARCHAR(128)
				DECLARE @SnapshotName VARCHAR(128)
				SET @DatabaseName = DB_NAME()
				SET @SnapshotName = DB_NAME() + ''_' + @Suffix + 
				''' 
 
				--table variable to hold file list
				DECLARE @DatabaseFiles TABLE (id INT identity(1,1),name VARCHAR(128), physical_name VARCHAR(400), ss_file_name VARCHAR(4000)) 
 
				--populate table variable with file information
				INSERT INTO @DatabaseFiles (name, physical_name, ss_file_name)
				SELECT name, physical_name, REVERSE(SUBSTRING(REVERSE(Physical_name),CHARINDEX(''\'', REVERSE(physical_name),0),LEN(physical_name)))
				FROM sys.master_files
				WHERE type != 1 AND database_id = DB_ID()
 
				--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

					IF ''' + @FilePath + 
				''' = ''''
					BEGIN
						SELECT @SnapshotScript = @SnapshotScript + ''(NAME = '' + QUOTENAME(name) + '', FILENAME = '''''' + ss_file_name + @SnapshotName + ''.ss''''),''
						FROM @DatabaseFiles
						WHERE id = @LoopCounter
					END 
					ELSE
					BEGIN
						SELECT @SnapshotScript = @SnapshotScript + ''(NAME = '' + QUOTENAME(name) + '', FILENAME = ''''' + @FilePath + ''' + @SnapshotName + ''.ss''''),''
						FROM @DatabaseFiles
						WHERE id = @LoopCounter
					END
				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 + '''
				--PRINT @SnapshotScript
				EXEC (@SnapshotScript)'

			EXEC (@SQL)

			FETCH NEXT
			FROM databases_curr
			INTO @Databases
		END

		CLOSE databases_curr

		DEALLOCATE databases_curr
	END
END