sp_dynamicDBSnapshotCreator DDL - A copy of the DDL for a stored procedure that easily creates snapshots of a database in SQL server (no specifying filename etc.) - modified to include date & time in the snapshot name.

sp_dynamicDBSnapshotCreator.sql

USE [WW2_DEV]
GO
/****** Object:  StoredProcedure [dbo].[sp_DynamicDBSnapshotCreator]    Script Date: 20/05/2021 8:42:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*******************************************************************************************************************************************
Dynamically Create SQL Server Database Snapshots v1.0 (2016-04-11)

Feedback: mailto:[email protected]

License: 
	sp_DynamicDBSnapshotCreator is free to download and use for personal, educational, and internal corporate purposes. 
	Redistribution or sale of sp_DynamicDBSnapshotCreator, in whole or in part, is prohibited without the author's express written consent.

Test: EXEC [dbo].[sp_DynamicDBSnapshotCreator] 'WW2_DEV'
*********************************************************************************************************************************************/
ALTER PROC [dbo].[sp_DynamicDBSnapshotCreator] (
	@sqldbname varchar(500)
) AS

SET NOCOUNT ON;
		
DECLARE @sqlvar nvarchar(3500), @dbvar nvarchar(500), @var0 int = 0
DECLARE @sqlvar1 nvarchar(max), @file_name VARCHAR(800), @path varchar(1510)
declare @dateString nvarchar(255) = replace(convert(nvarchar(255), getdate(),102),'.','_') + '_' + replace(convert(nvarchar(255), getdate(),108),':',''); 

SET @sqlvar = N'
	SELECT 
		[name]
		,[physical_name]      
	FROM ['+@sqldbname+'].[sys].[database_files]'

-- all database snapshots created via this script will be suffixed with _snap
SET @dbvar = @sqldbname + '_ss_' + @dateString;
SET @sqlvar1 = N'
CREATE DATABASE '+@dbvar+' 
ON'

DECLARE @temptab AS TABLE ([name] VARCHAR(800), [physical_name] VARCHAR(1510))  
	INSERT into @temptab EXECUTE sp_executesql @statement=@sqlvar
DECLARE sp_execDBSnap_cursor CURSOR FOR
	SELECT * FROM @temptab
OPEN sp_execDBSnap_cursor
FETCH NEXT FROM sp_execDBSnap_cursor 
INTO @file_name, @path

WHILE @@FETCH_STATUS = 0
BEGIN    
	-- exclude log files
	IF CHARINDEX('.ldf',@path) = 0
	BEGIN
		IF @var0 = 0
		BEGIN
			SET @sqlvar1 = @sqlvar1 + N'
			( NAME = '+@file_name+', FILENAME = '''+replace(@path,'.mdf','.ss')+''' )'
		END
		ELSE
			BEGIN
				SET @sqlvar1 = @sqlvar1 + N'
				,( NAME = '+@file_name+', FILENAME = '''+replace(@path,'.mdf','.ss')+''' )'
			END
	END	
	SET @var0 = @var0 + 1

    FETCH NEXT FROM sp_execDBSnap_cursor 
    INTO @file_name, @path
END 
CLOSE sp_execDBSnap_cursor;
DEALLOCATE sp_execDBSnap_cursor;

SET @sqlvar1 = @sqlvar1 + N'
	AS SNAPSHOT OF '+@sqldbname+''

IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = ''+@dbvar+'' )
BEGIN
	-- run create database snapshot script 
	EXECUTE sp_executesql @statement=@sqlvar1
END
ELSE -- return snapshot exists message
BEGIN
	PRINT 'database snapshot '+@dbvar+', already exists!'
END