Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Database Snapshots in SQL Server 2005

By Arvinder Khosla,

Database Snapshots in SQL Server 2005

By Arvinder Singh Khosla and S.Srivathsani Murthy


Introduction

Database snapshots is a new feature added in SQL Server 2005.Database Snapshot can be used to create a read-only copy of the database. Database snapshot consists of the read-only static view of the database without including the uncommitted transactions. The uncommitted transactions are rolled back to make the database snapshot transactionally consistent.

Uses:

Database snapshots can be used for

  • Protecting your system from user or administrator error
  • Offloading reporting
  • Maintaining historical data
  • System upgrades
  • Materializing data on standby servers
  • Recovering data

How Database Snapshots Work?

Database snapshots occur at the page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.
This is illustrated with the help of this diagram:

  1. At first when the snapshot is created, the sparse file is empty.

  2. When the first update operation happens, the original data is copied into the sparse file. The database engine then updates the data in the source database.

When the first update operation happens, the original data is copied into the sparse file. The database engine then updates the data in the source database.
During the read operation on the snapshot, the original data is read from the source database. For the data which has been changed, the snapshots read from the sparse file.

Advantages

  1. 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. This will be useful in auditing scenarios
  2. The snapshots can be used in restore operations.
  3. The corrupted or deleted data can be recovered from the snapshot to repair the primary database.
  4. In case of user error, the administrator can revert back to the snapshot taken just before the error.

Disadvantages

  1. Database snapshots are available only in the SQL Server 2005 enterprise edition.
  2. Database snapshots are dependent on the primary database. If the primary database goes offline, then the snapshots are not accessible.
  3. 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 updated.
  4. Full-text indexing is not supported in database snapshots.
  5. If the data changes rapidly, the snapshot might run out of disk space.

Creating a Database Snapshot-TSQL

In the PUBS database we have 3 data files under 2 file groups:

  1. Primary File group contains Pubs and pubs_logicalname
  2. Pubs_data filegroup (Secondary Filegroup) contains Pubs_2
The statement below creates a snapshot on the PUBS database

CREATE DATABASE pubs_Snapshot1800 ON 
(Name=pubs,Filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_data1800.ss'),
(Name=pubs_logicalname,Filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_logicalname_data1800.ss'),
(NAME = pubs_2, 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs2_data1800.ss') 
AS SNAPSHOT OF pubs

In pubs database, there are three files. The user should include all the three files while taking the snapshot. The database is a combination of data present in all three files. So we need to mention the all the three logical file names, so that all the data from these files will get stored in the location 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\filename_with_timestamp.ss'. In the example given above,
  • pubs_Snapshot1200 --> Snapshot Name
  • pubs --> Logical File Name
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind_data1000.ss --> Physical File Path
Suppose if the user wants to create the snapshot at 2400 hrs then he has to just change the name of the database and the physical file name.

CREATE DATABASE pubs_Snapshot2400 ON 
(Name=pubs,Filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_data2400.ss'),
(Name=pubs_logicalname,Filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_logicalname_data2400.ss'),
(NAME = pubs_2, 
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs2_data2400.ss') 

The snapshot can be used just like the database. The user has to remember that the snapshot is a read-only copy of the database. So he/she cannot issue the update, delete or insert commands.

Viewing Snapshots from SQL Server Management Studio

  1. Expand Databases.
  2. Expand Database Snapshots.
  3. Select the snapshot created in the above section

Restoring from the Database Snapshot

Consider the database Pubs. Suppose the user deletes dbo.table_1 from the Pubs database. We have to recover it from the database snapshot by issuing the following command.


Restore database pubs from database_snapshot = 'pubs_Snapshot1800'

Dropping the Snapshot


Drop Database 'Snapshot_Name'

Frequently Asked Questions on Snapshots

Question 1. How is Snapshot different from the full backup of the database?
Answer 1. Differences are as follows:

  1. In case of backup, we have to restore it somewhere to actually use it. The user cannot query the backup file. On the other hand snapshots are static read only data at that point of time. So it can be used just like any other database.
  2. Another advantage or difference that snapshots have over backups is that backups occupy lot of space.
    For example: Suppose if there is a database in which only few updates takes place. Then definitely snapshot is a better option because less data has to be stored in the sparse file.

If we remember that in case of snapshots, only the pages which are updated are stored in the sparse file (refer above). When we fire the select query, the snapshots will read the pages which are not changed from the source database and the changed ones are read from the sparse file. Thus snapshot proves more advantageous than full backups in these scenarios.

Question 2. Is ss the default extension of the data files?
Answer 2. The default extension is not ss. It can be anything

Question 3. When to use Snapshot and when to use Full backups? And are differential backups influenced / dependent on Snapshot?
Answer 3.
Backups and Snapshots are two different concepts. Snapshots are definitely NOT a replacement for backups. Snapshots should be used for auditing or reporting purposes. Recovery operations can be done with the help of snapshots. But it is not the primary resource. Snapshots provide a read only view of the database at that point of time. It does not include uncommitted transactions. When we take backups, the uncommitted transactions are also included in the backups.
Differential backups are not dependent on snapshots. Differential backup is the differential of the last full backup and the current state of the database.

Got more questions?
Please mail us (Arvinder Singh Khosla or Srivathsani Murthy)
Total article views: 13847 | Views in the last 30 days: 19
 
Related Articles
ARTICLE

Database Snapshot and Rollback Transactions

A Rolled back transaction will increase the snapshot sparse file usage. New author Subash brings us ...

ARTICLE

Database Snapshots

In the first part of his series on SQL Server Availability, new author Kumar Parthasarathi brings us...

FORUM

Streams VS Sparse File

Streams VS Sparse File

FORUM

Maintenance plan backup activity screwed by adding a snapshot

When adding a snapshot database to the DB server the maintenance plan set to backup all databases fa...

FORUM

Database SnapShot

Database SnapShot

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones