Recover Lost Data using Database snapshot in SQL Server

Database snapshot was firstly introduced in SQL server 2005 enterprise edition. It is a static view of SQL server database (source database) and read-only in nature. It resides on the same instance as its source database. When a source database is updated then its snapshots database are also updated. Database administrator can create database snapshot for all source database irrespective of their recovery model. It can be used for reporting purpose and quick recovery of SQL data. Here, you will see how to create and use database snapshot features.

How to Create a Source Database?

Database administrator can create a source database by executing following T-SQL command:

Use master

GO

CREATE DATABASE Name-Source-Database

GO

# Create a table in the source database

Create Table-Name in Name-Source-Database

Note: Replace the Name-Source-Database & Table-Name with the name of your source database & table name.

How to Create a Database Snapshot?

After the creating of source database, now database administrator can create the database snapshot for its source database by executing following T-SQL command:

Use master

GO

CREATE DATABASE Name-Source-Database_Snapshot

ON

(

NAME = 'Name-Source-Database',

FILENAME = 'D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAName-Source-Database_Snapshot.SS')

AS

SNAPSHOT OF Name-Source-Database

GO

Note: Database administrator can't create database snapshot with the help of SQL server management studio (SSMS).

How to Recover?

If database administrator has lost data from the source database due to any reasons then deleted data can be recovered by two methods: Full Backup & Database snapshot. Full backup is out of scope of this article so here you will see how to recover data by database snapshot.

Recovery with Database Snapshot

Database snapshot's Insert into...Select options helps in recovery of deleted record from the table.

Database administrator can restore source database with the help of database snapshot. To restore the database, execute the following T-SQL command:

USE master

GO

RESTORE DATABASE Name-Source-Database

FROM DATABASE_SNAPSHOT ='Name-Source-Database_Snapshot'

GO

Now use Insert into...Select option to recover the lost data.

INSERT INTO Name-Source-Database.dbo.record

SELECT * FROM Name-Source-Database_Snapshot.dbo.record

GO

Advantages of Database Snapshot:

• It helps in reporting purpose.
• It safeguards the database against user & administrator error.
• It is used for maintaining the historical data.
• It helps in creating multiple database snapshots of a given source database.
• Flexibility to create database snapshot for all source database irrespective of their sizes.