Snapshot database is one of the most interesting concepts that I have used at some places recently.
Here is a quick definition of the subject from Book On Line:
A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.
If you do not know how Snapshot database work, here is a quick note on the subject. However, please refer to the official description on Book-on-Line for accuracy. Snapshot database is a read-only database created from an original database called the “source database”. This database operates at page level. When Snapshot database is created, it is produced on sparse files; in fact, it does not occupy any space (or occupies very little space) in the Operating System. When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases. When an unmodified data page is read in the Snapshot database, it actually reads the pages of the original database. In other words, the changes that happen in the source database are reflected in the Snapshot database.
Let us see a simple example of Snapshot. In the following exercise, we will do a few operations. Please note that this script is for demo purposes only- there are a few considerations of CPU, DISK I/O and memory, which will be discussed in the future posts.
- Create Snapshot
- Delete Data from Original DB
- Restore Data from Snapshot
First, let us create the first Snapshot database and observe the sparse file details.
USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
Now let us see the resultset for the same.
Now let us do delete something from the Original DB and check the same details we checked before.
-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
When we check the details of sparse file created by Snapshot database, we will find some interesting details. The details of Regular DB remain the same.
It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database. This is the reason why the size of the snapshot DB is increased.
Now let us take this small exercise to the next level and restore our deleted data from Snapshot DB to Original Source DB.
-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO
Now let us check the details of the select statement and we can see that we are successful able to restore the database from Snapshot Database.
We can clearly see that this is a very useful feature in case you would encounter a good business that needs it.
I would like to request the readers to suggest more details if they are using this feature in their business. Also, let me know if you think it can be potentially used to achieve any tasks.
Complete Script of the afore- mentioned operation for easy reference is as follows:
USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
68 Comments. Leave new
One thing is that Database Snapshot is available in Enterprise Edition of SQL Server.
So you can use this functionality only if you have Enterprise Edition of SQL Server.
Hi Pinal,
How can we recover the 10 day’s before data from snapshot?
How can we schedule snapshot?
Thanks pinal for the article. I use the sanpshot feature in OLTP environments. Situations where one needs to do a large update, I usually create a snapshot of the original db prior to the update being applied. In case the large update messes up the original db, I have the snapshotdb to get back my original data.
Hi Pinal,
Thanks for this article, and for your blog in general !
We use database snapshots with mirroring, because it seems to be the only way to access the mirrored database as this database remains in restoration mode all the time.
So, with a SQL job, we regularly recreate a database snapshot to use it with Reporting Services.
Hi just one simple query in case of multiple snapshots will it be increasing the size of each database as the changes progress in the future.
Hi Pinal ,
I am facing problem while creating Snapshot Of My DB
Scenario–>
–My Sql SERVER Is On Network Drive named ‘\\xeon-s2′
–My DbId =630
SELECT physical_name FROM master.sys.master_files WHERE database_id = 630 AND FILE_ID = 1
–Query returns F:\Rbhargava_CI\Rbhargava_FV_CI.mdf
CREATE DATABASE Rbhargava_FV_CI_SNAPSHOT ON
(Name =’Rbhargava_FV_CI’,
FileName=’\F\Rbhargava_CI\rk.ss’)
AS SNAPSHOT OF Rbhargava_FV_CI;
–Error:
Msg 5014, Level 16, State 3, Line 1
The file ‘Rbhargava_FV_CI’ does not exist in database ‘Rbhargava_FV_CI’.
modify u r qury as
CREATE DATABASE Rbhargava_FV_CI_SNAPSHOT ON
(Name =’Rbhargava_FV_CI’,
FileName=’F:\Rbhargava_CI\rk.ss’)
AS SNAPSHOT OF Rbhargava_FV_CI;
and try it out. I hope it will work.
Regards
Ramesh
Thanks for the much informative article Pinal.
Hi,
i am getting this error msg can any 1 help..
The file ‘people_db’ does not exist in database ‘people_db’.
use your database logical file name(Name of your .mdf file)
Right click on database->properties->files.
alternatively you can use
use ‘yourdb’
select name from sys.database_files.
Thanks,
Deepraj
Also the second resultset of EXEC sp_helpdb ‘dbname’
IT IS POSSIBLE TO GET PARTICULAR
DELETED RECORDS FROM LOG FILE
Is there a possibility to have the Snapshot stored as a flat file so that it can be sent offsite?
Thanks,
Sean
Hello sir,
Can you put a article on how to take care of incremental data on RegularDB(Example Db). with added data to RegularDB, how we can keep snapshotDB updated. it it is job then what will be steps…or what either way is available…. Thank you in advance for you kind help ….you are always great ..Sql Guru
So I am getting an error message after the snapshot DB is created:
Executed as user: XXX. Failed to update database “XXX_Reporting” because the database is read-only. [SQLSTATE 25000] (Error 3906). The step failed.
The snapshot is successfully created but it causing the job to be in a failed state. Any ideas?
USE [master]
GO
/****** Object: Database [XXX_SIT_Reporting] Script Date: 10/04/2010 08:36:26 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’XXX_SIT_Reporting’)
DROP DATABASE [XXX_SIT_Reporting]
GO
/****** Object: Database [XXX_SIT_Reporting] Script Date: 10/04/2010 08:35:57 ******/
CREATE DATABASE [XXX_SIT_Reporting] ON
( NAME = N’XXX_SIT’, FILENAME = N’E:\MSSQL\Data\XXX_SIT_Reporting.ss’ ) AS SNAPSHOT OF [XXX_SIT]
GO
We just started using Snapshots here at work, and they are great. Currently, we have a Job that takes a snapshot of the databases prior to our nightly Full Backup. This serves two purposes for us. First, it allows us to have a pretty recent, already mounted, copy of the database that is ready to go in case we need to restore some data, such as when a careless developer ‘accidentally’ drops a table. Secondly, it allows for our support team to be able to use the Snapshot copy for investigative purposes when issues arise, rather then having to drag down performance on the production database. Since it is Read-Only, I don’t have to worry about them altering any live data. I am also looking at moving our heaviest reports to run on Snapshots, so as to remove the stress these reports generate from our main databases.
very nice article.. n easy understandable the use of db snapshot..
thank you!
I am trying following but it is giving me an error
— Create Snapshot Database
CREATE DATABASE SnapshtDB ON
(Name =’SandBox’,
FileName=’c:\SSDB.ss1′)
AS SNAPSHOT OF SandBox;
GO
I get the following error
Msg 5127, Level 16, State 1, Line 2
All files must be specified for database snapshot creation. Missing the file “SandBox2”.
Hi Cool Queries to understand the fundamentals of snapshot,
thanks a lot
joseph
Simple question:
Can I restore just one table from snapshot database?
Thanks,
Jekic
I have a solution:
This is INSERT INTO statement from snapshot database to source database.
Easy to understand with an example, thank you for your artcile
Dear Sir
Today i was trying to attach the (MDF,NDF,LDF ) sql server 2008 database which i have received from my client. After attachment the database status is showing (Read-Only) (Eg.database name (Read-Only). How do i make to normal mode for the data updation. is there any query available to resolve this problem. Your help will be highly helpful.
When we do large data loads (> 400gb) our snapshots become suspect. Anyone else have that issue?
Hello Pinal.
I was thinking about using the snapshot database for reporting purpose..but it is read only and dont get updated when live database is updated..is there other option where we can create a copy of the live database and updated the copied database whenever live database gets updated… please advice!..Thanks…
Snapshot are read only. To create a near time reporting database look into log shipping. Your target database can be read only and in continuous recovery, so the latest transactions are applied.