SQL Server – How to create snapshot database

A database snapshot is the static view of the source database with ready only permission. We can’t modify the snapshot, must be on the same server instance of the source database. We are using snapshot for reporting purpose for the users.
After having snapshot, if any manual failure occurs that can be revert back to its earlier state by applying database snapshot.
Database snapshots works on page level. Any modification related to data page gets copied on snapshot first this process known as copy on write operation. Anything which gets modified on source database doesn’t affect to the snapshot created on that database.

Why should we create database snapshot?

1. To maintain historical data for management to investigate grow and to generate different reports like sales report for last three months, productivity reports so on.
2. Database snapshot can be used as offline reporting to avoid the workload on the source database.
3. To avoid the Administrative error and user error

Snapshot database

In this article will demonstrate the working on database snapshot for that we are creating the snapshotdb database with dummy tables for demonstration.
1. Lets create a snapshot database for demo
Create database Snapshotdb

2. Creating table using below script in ‘snapshotdb’ database

create table emp_details
emp_id int,
emp_name varchar(50),
emp_add varchar(20),
emp_deg varchar(20)

3. Populating dummy data in the above table using below script

insert into emp_details

insert into emp_details

insert into emp_details

4. To find out location of the data files, run the below script in database

Select * from sys.sysfiles

5. Now we are creating the snapshot by using T-SQL, Here the parameter “name”should be data file name of the source database. Use above query to find out name of data file or using sp_helpdb sourcedatabase

create database SnapshotDB_20092011 on
( name = SnapshotDB, filename =’D:\SnapshotDB_20092011.ss’ )
as snapshot of SnapshotDB;

6. Lets say, after creating snapshot user has updated some data in the table.

update emp_details
set emp_deg = ‘Manager’
where emp_name = ‘Ramesh’

Now lets see whether data is updated in snapshot or not. Click on database snapshot folder you will snapshot is created
Now right click on snapshot -> New query

use SnapshotDB_20092011

Select * from emp_details

In output, you can see data is not updated.

When you will try to update snapshot database, it will throw below error which says that database is read only.

Msg 3906, Level 16, State 1, Line 2
Failed to update database “SnapshotDB_20092011” because the database is read-only.

Now question is how to restore database snapshot in sql server ?

Leave a Reply




8 − 7 =