Recover Data Using Database Snapshots
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.
CREATE DATABASE SourceDB_Snapshot
NAME = ‘SourceDB’,
FILENAME = ‘D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATASourceDB_Snapshot.SS’
SNAPSHOT OF SourceDB
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.
DELETE FROM Address
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.