Create a Snapshot of Your Database

In order to create a Snapshot of your database, you can use the following code:

DECLARE @DBName VARCHAR(100) = 'DBName'

DECLARE @CurrentDate VARCHAR(20)
SET @CurrentDate = '_' + CONVERT(VARCHAR(20), GETDATE(), 120)
SET @CurrentDate = REPLACE@CurrentDate, ':', '')
SET @CurrentDate = REPLACE(@CurrentDate, '-', '_')
SET @CurrentDate = REPLACE(@CurrentDate, ' ', '_')

DECLARE @sql VARCHAR(MAX) 
SET @sql = ''
SELECT
   @sql = COALESCE(@sql + ('(NAME=' + [name]
                           + ',FILENAME=''C:Snapshots' + [name]
                           + @CurrentDate + '.ss''),'), '')
FROM
   sys.database_files
WHERE
   type_desc = 'ROWS'
ORDER BY
   [file_id] 
    
    SET @sql = LEFT(@sql, LEN(@sql) - 1)
SELECT
   'CREATE DATABASE '+ @DBName + @CurrentDate +' ON ' + @sql + 
   ' AS SNAPSHOT OF ' + @DBName 
ROM
sys.master_files
WHERE
database_id = DB_ID(@DBName) AND
type_desc = ‘ROWS’