What is Database Snapshots?
Like the snapshot of Camera, Database snapshots are the snapshot of database at particular interval of time. It is read only copy of the whole database. This snapshot does not contain uncommitted transaction at the time of taking snap of the database. To make the source data base consistent with the snapshot all the uncommitted transaction must be roll backed.
Advantages of Database Snapshots
- The user can create as many snapshots as he/she wants quickly in no amount of time. The user can schedule to take snapshots every hour.
- The snapshots can be used in restore operations.
- The corrupted or deleted data can be recovered from the snapshot to repair the primary database.
- In case of user error, the administrator can revert back to the snapshot taken just before the error.
- When you query a snapshot, you will not experience blocking due to update/insert operations that you might have to contend with when querying the source database.
Database snapshots are available only in the SQL Server 2005 and higher (enterprise edition).
- Database snapshots are dependent on the primary database. If the primary database goes offline, then the snapshots are not accessible.
- Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updwated.
- Full-text indexing is not supported in database snapshots.
- If the data changes rapidly, the snapshot might run out of disk space. If a database snapshot runs out of space, it is marked as SUSPECT, and it must be dropped.
- You cannot grant a new user access to the data in a snapshot. Permissions are inherited form the source database as it existed at the time of snapshot creation.
- Because of the reliance on the source database, the snapshot has to exist on the same server as that source database.
How it works?
Just after you create the snapshot database, that database will be allocated to an empty sparse file. Whenever there are changes to the original data pages, the original page are moved to the spare file. If you access the database snapshot, it will be read from the sparse file and from the original database data pages which have not changed.
Working with Database Snapshots
Download the Database “AdventureWorks2012” from Microsoft website. First you need to create a database snapshot.
CREATE DATABASE AdventureWorks2012_Snapshots ON
( NAME = AdventureWorks2012_Data,
FILENAME ='C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Snapshots.ss'
AS SNAPSHOT OF AdventureWorks2012;
In the above example, “AdventureWork2012_Snapshots.ss” is the sparse file. The extension ‘ss’ is just an arbitrary value and it is not a default or required. “AdventureWorks2012_Snapshots” is the name of Snapshots database, “AdventureWorks2012_Data” is name of Name of Actual Database file and “AdventureWorks2012” Is the name of the database on which snapshot is created. Filename is the actual path where Database file of your system resides. Run the script as shown in the figure.
After running the script the database snapshot will be created as shown in the following-
Accessing this database snapshot is as same as any other database.
SELECT * FROM AdventureWorks2012_Snapshots.Sales.Customer
Like accessing a database, dropping the database snapshot is the same as a normal database drop.
DROP DATABASE AdventureWorks2012_Snapshots
You have the option of restoring a database snapshot into the current database. In that case, database snapshots can be treated as a database backup. Do not use the connection of AdventureWorks2012 while restoring the database instead use master database of SQL Server.
RESTORE DATABASE AdventureWorks2012 from
DATABASE_SNAPSHOT = 'AdventureWorks2012_Snapshots'
Scheduling Database Snapshot Creation
Scheduling a database snapshot is very important as there is no direct options from the database snapshot node in the SQL Server Management Studio. However, by using a SQL Server agent job you can create a schedule to create database snapshot automatically on specified intervals.
The following script will create Procedure. The first step is to drop the database snapshot while the second is to create a database snapshot again. This job will create a database snapshot once every hour.
CREATE PROCEDURE Procedure_AdventureWorks2012_Create_Snapshots
declare @Statement nvarchar(500)
if exists (select * from sys.databases where name = ‘AdventureWorks2012_Snapshots’)
DROP DATABASE AdventureWorks2012_Snapshots
‘CREATE DATABASE AdventureWorks2012_Snapshots ON
(NAME = AdventureWorks2012_Data,
FILENAME =”C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Snapshots.ss”
AS SNAPSHOT OF AdventureWorks2012’
exec sp_executesql @Statement
As shown in the image
Creating a SQL Server Job: Right Click on Job in SSMS>SQL Server Agent as shown and click on New Job as shown
A screen as shown will appear now type the name of job and click on “Step” marked with red color to create a new step under job.
Now provide the name of Step as it is given in image and choose the Database on which procedure was created earlier and in command box write
Click on Advance Button marked with red color and select Quit the job reporting success in “on success action” and click on OK button to create the step, the step dialog box will be closed.
Now Click on Schedules and then on New to create a scheduler for the job
Now provide the name of the scheduler as shown in the image and choose the required scheduling frequency and time as per your requirement (the image can be referred for choosing scheduling frequency and time) click on OK to finish the task of creating the Scheduler and then again ok for creating the job.
Now see the new job is created which will automatically create snapshots every hours. This complete “Chapter-3-Working on Database Snapshots (SQL Server 2012)”.