devxlogo

Create a Snapshot of Your Database

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_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?

devx-admin

Share the Post: