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:
CREATE DATABASE Name-Source-Database
# 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:
CREATE DATABASE Name-Source-Database_Snapshot
NAME = 'Name-Source-Database',
FILENAME = 'D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAName-Source-Database_Snapshot.SS')
SNAPSHOT OF Name-Source-Database
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:
RESTORE DATABASE Name-Source-Database
FROM DATABASE_SNAPSHOT ='Name-Source-Database_Snapshot'
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
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.