dbo.sp_Snapshot.sql

USE master;
GO

IF OBJECT_ID (N'dbo.sp_Snapshot', 'P') IS NULL
    EXECUTE('CREATE PROCEDURE dbo.sp_Snapshot AS SELECT 1');
GO

ALTER PROCEDURE dbo.sp_Snapshot(
      @databaseList NVARCHAR(4000)
    , @listOnly     BIT = 0
    , @debug        BIT = 1
)
AS
/*
EXAMPLE
EXEC dbo.sp_Snapshot
     @databaseList = 'AdventureworksDW2016CTP3,AdventureworksTest'
   , @debug = 0;

NOTE
  Github: https://github.com/ktaranov/sqlserver-kit/blob/master/dbo.sp_Snapshot.sql
  Original Link: https://sqlundercover.com/2017/09/20/sp_snapshot/
  Author: David Fowler
  Created Date: 2017-09-19
  Modified: 2017-09-28 Konstantin Taranov
  (c) SQLUndercover 2017
*/
BEGIN

DECLARE @TSQLStatement NVARCHAR(MAX) = N'';

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 Item AS name
    FROM master.dbo.udf_SplitStringByDelimiter(@databaseList, ',');
-- https://github.com/ktaranov/sqlserver-kit/blob/master/User_Defined_Function/udf_SplitStringByDelimiter.sql

--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 NVARCHAR(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
        SET @TSQLStatement = 'USE ' + @Databases + ';' + CHAR(13) +
            '
            DECLARE @DatabaseName NVARCHAR(128);
            DECLARE @SnapshotName NVARCHAR(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 NVARCHAR(128), physical_name NVARCHAR(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 NVARCHAR(2000);
            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 sp_executesql @snapshotScript;';

            SET @TSQLStatement = REPLACE(@TSQLStatement, '            ', '');

            IF @debug = 1 PRINT @TSQLStatement;
            ELSE
            EXEC sp_executesql @TSQLStatement;
 
        FETCH NEXT FROM databases_curr
        INTO @Databases;
    END;
 
    CLOSE databases_curr;
    DEALLOCATE databases_curr;
END;
END;
GO