×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Create a Snapshot of Your Database
 

Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Thursday Feb 1st 2018 by Hannes du Preez

Language: SQL, Level: Intermediate: - Use this code to create a snapshot of your current 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’
Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved