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_filesWHERE 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 ROMsys.master_filesWHEREdatabase_id = DB_ID(@DBName) ANDtype_desc = ?ROWS?
