The idea for this script came at a time when I was having a project that needed to use snapshots of databases frequently. For that reason I needed to create a T-SQL code that would make snapshots for me instantly. I searched the web for this, but didn’t find something that was suitable for my project works, and decided to write my own script. The script was firstly published in the SqlServerCentral repository.

/***************************************************************************
Snapshot script:
    The script enables a quick creation of snapshots for databases. 
    It has two "run modes" defined by the optional parameter @exec_mode:
      @exec_mode=0 (default) means printing the create T_SQL statement;
      @exec_mode=1 means creation of the snapshot database;
      @database is mandatory input parameter.
    
    Tested on SQL Server 2008, 2008 R2, 2012, 2014, 2016
    EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorks2014'
    EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012', @exec_mode=0
    EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorks2014', @exec_mode=1
Date created:      2014-Mar-12
Author:            Igor Micev
***************************************************************************/USE [master];
GO
IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[Create_UserDb_Snapshot]') AND [type] IN(N'P',N'PC'))
BEGIN
    DROP PROCEDURE [dbo].[Create_UserDb_Snapshot]
END;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[Create_UserDb_Snapshot]
@database VARCHAR(50),@exec_mode BIT=0
AS 
BEGIN
    SET NOCOUNT ON;    
    DECLARE @err_text NVARCHAR(200);
    
    IF @database IN('master','model','tempdb')
    BEGIN
        SET @err_text='Snapshot is not allowed for master, model and tempdb databases.';        
        RAISERROR(@err_text,10,1);        
        RETURN;
    END;
    
    IF OBJECT_ID('tempdb..#usr_db_files_tbl') IS NOT NULL
    BEGIN
        DROP TABLE #usr_db_files_tbl
    END;
    
    CREATE TABLE #usr_db_files_tbl (
     [logicalName] VARCHAR(100),[physicalName] VARCHAR(200)
    );
    
    INSERT INTO #usr_db_files_tbl(logicalName,physicalName)
    SELECT [mf].[name],[mf].[physical_name]
    FROM sys.master_files AS [mf]
    WHERE [mf].[database_id]=DB_ID(@database) AND [mf].[type]=0 AND [mf].[name] NOT IN('master','model','tempdb');
    
    IF(SELECT COUNT(*) FROM #usr_db_files_tbl)=0
    BEGIN
        SET @err_text='The specified database does not exist.';        
        RAISERROR(@err_text,10,1);        
        RETURN;
    END;
    
    DECLARE @dyn_sql NVARCHAR(MAX);
    DECLARE @extension VARCHAR(20)='snap'+RIGHT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),113),'-',''),':',''),' ',''),4);
    DECLARE @curLogicalName VARCHAR(100),@curPhysicalName VARCHAR(200);
    DECLARE snap_cursor CURSOR FOR SELECT [t].[logicalName],[t].[physicalName]
                                   FROM #usr_db_files_tbl AS [t];
    
    OPEN snap_cursor;    
    DECLARE @snapDbName NVARCHAR(100);    
    SET @snapDbName=@database+'_SS_'+REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),113),'-',''),':',''),' ','');
    
    FETCH NEXT FROM snap_cursor INTO @curLogicalName,@curPhysicalName;    
    SET @dyn_sql='CREATE DATABASE '+@snapDbName+' ON '+CHAR(10);
    
    WHILE(@@FETCH_STATUS=0)
    BEGIN
        SET @dyn_sql+='(name='''+@curLogicalName+''', filename='''+REPLACE(REPLACE(@curPhysicalName,RIGHT(@curPhysicalName,4),'.'+@extension),RIGHT(@curPhysicalName,4),'.'+@extension)+''')';        
        FETCH NEXT FROM snap_cursor INTO @curLogicalName,@curPhysicalName;        
        IF(@@FETCH_STATUS=0)
        BEGIN
            SET @dyn_sql+=', '+CHAR(10)
        END;
    END;
    
    SET @dyn_sql+=CHAR(10);    
    SET @dyn_sql+='AS SNAPSHOT OF '+@database+CHAR(10);
    
    IF @exec_mode=CONVERT(BIT,0)
    BEGIN
        PRINT CHAR(10)+@dyn_sql
    END;
    
    IF @exec_mode=CONVERT(BIT,1)
    BEGIN
        BEGIN TRY
            EXEC sp_executesql @dyn_sql;            
            PRINT 'Snapshot of database: '+@snapDbName+' created successfully.';
        END TRY
        BEGIN CATCH
            SET @err_text='Snapshot of database '+@database+' cannot be created.';            
            RAISERROR(@err_text,16,1);
        END CATCH;
    END;
    
    CLOSE snap_cursor;    
    DEALLOCATE snap_cursor;
    
    IF OBJECT_ID('tempdb..#usr_db_files_tbl') IS NOT NULL
    BEGIN
        DROP TABLE #usr_db_files_tbl
    END;
    
    WAITFOR DELAY '00:00:01';   
    RETURN;
END;