Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Tip: Create a Snapshot of Your Database

Use this code to create a snapshot of your current database.


advertisement

WEBINAR:

On-Demand

Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js


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’

 

Visit the DevX Tip Bank

 





   
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date