articles >> general dba >> Recover Data Using Database Snapshots

Recover Data Using Database Snapshots

By : Ashish Kumar Mehta Dec 02, 2008

Page 2 / 3


Create Database Snapshots
Database Administrators can now create a database snapshot using the below mentioned TSQL code. However it is not possible to create a database snapshot using the SQL Server Management Studio GUI. 

Use master
GO

CREATE DATABASE SourceDB_Snapshot
ON
(
NAME = 'SourceDB',
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\SourceDB_Snapshot.SS'
)
AS
SNAPSHOT OF SourceDB
GO

Once the SourceDB_Snapshot database snapshot is created successfully it can be viewed by expanding the Databases node and then finally by expanding Database Snapshots node as shown in the below snippet. In order to see all the objects within the database snapshot you can expand SourceDB_Snapshot database snapshot. The important thing to note is that it took less than a second to create the snapshot database which is comparatively much less time than taking a Full backup of database. 


 
Let us check the space occupied by the SourceDB_Snapshot.SS sparse file which was created using the above TSQL code.

 

You could see that the disk space occupied by the SourceDB_Snapshot.SS sparse file is 128 KB.

Delete Few Records from Address table of SourceDB Database
Next step will be delete few records from Address table which is available in SourceDB database by executing the below the below TSQL query.

USE SourceDB
GO
DELETE FROM Address
GO

You could see that instead of deleting few records from SourceDB database by mistake you ended up deleted all the records from Address table this can very well happen in a real life scenario.