Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Snapshots for Data Comparison


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

I want to be able to update my application and have the SQL Server data from before the upgrade available as a reference after the upgrade in case there is a problem. Copying the data to another server is prohibitive because of the size.  Also, the backup and restore operation takes hours that we simply do not have.  Are there any options available in SQL Server?

Solution

SQL Server 2014 Enterprise Edition offers database snapshots which offer a point in time, read-only view of a database which can be created quickly before the upgrade process and then utilized afterwards as a reference point. The SQL Server database snapshot can be created in a few short minutes for even the largest of databases and is accessed much like a regular database.

How do SQL Server Database Snapshots work?

Database Snapshots were released with SQL Server 2005 as an Enterprise Edition feature and continue with subsequent versions. Microsoft makes a snapshot database available by utilizing a copy-on-write technology that copies 8kb data pages that are changed to a sparse file and then read back when that information is requested from the snapshot by a client application such as SQL Server Management Studio. Microsoft implements a bitmap in cache which keeps track of every page in a data file. When a request is received SQL Server checks the bitmap to understand whether the page has changed and whether to access the snapshot sparse file or the original data file. If SQL Server is restarted the bitmap is rebuilt (1). In the image below we can see original pages in blue when the snapshot is first created at 7am.

Using SQL Server Database snapshots as part of deploying new code
image source: Microsoft SQL Server 2005 documentation

If a request were to come in at that moment we can see that the data being requested from the snapshot resides in the original data file, nothing has been copied to the snapshot file at this point. This is an important point to consider because we begin to understand how SQL Server uses the snapshot, the sparse file is only populated with original copies of data that has changed since the snapshot was created. Once users begin using the source database, SQL Server begins copying all 8kb pages which have changed to the snapshot file location. If a request to the snapshot database is received SQL Server pulls in the original 8kb page from the sparse file and the rest of the data from the database to fulfill the request. The image below illustrates this concept.  Red pages are pages that have changed and the blue pages are the original data which has been copied to the sparse file.

Implementing snapshots
image source: Microsoft SQL Server 2005 documentation

Implement SQL Server Database Snapshots

SQL Server Management Studio does not have an interface for creating the snapshot, but the T-SQL syntax is simple. For each data file there is a corresponding file which must be created with the "Create Snapshot" statement. The following code accomplishes this for the AdventureWorks2008R2 Database:

CREATE DATABASE AdventureWorks2008R2_Snap 
ON (NAME = AdventureWorks2008R2_Data, 
FILENAME = 'C:\DatabaseSnapshots\AdventureWorks2008R2_Data.ss' ) 
AS SNAPSHOT OF AdventureWorks2008R2

Notice there is only one file in our example. There is no log file needed for a snapshot, the snapshot is never recovered as a traditional database. The snapshot does, however, go through recovery when it is created. Since data files may have incomplete transactions committed, the snapshot creation process utilizes the transaction log to gain a point in time view of the data which has been committed. In this manner the snapshot file may be larger than expected when created. Additionally, if our example had more than one data file we would have needed multiple snapshot files.

Below we see how snapshots are displayed in the SQL Server Management Studio. Notice in the drop down there is nothing to indicate that a database is a snapshot other than naming convention.

See how snapshots are displayed in the SQL Server Management studio

There is nothing to indicate that a database is a snapshot

Querying SQL Server Database Snapshots

SQL Server database snapshots can be used much like the database they are built off of. You can run ad-hoc queries as you normally do or you can execute stored procedures as long as there is no data modification. Additionally, snapshots do not incur locking as you normally encounter because they cannot modify data. You can also run queries which join the snapshot and the source database to compare data. In our example below we have accidentally deleted 9 rows and needed to write a query comparing the snapshot and the source database. By having a snapshot on hand we can quickly locate the error.

Querying snapshots

Points to consider

  • Any user who can create a user defined database can create a snapshot.
  • Snapshots can be made of databases in an Availability Group.
  • Multiple snapshots can be created for each user defined database.
  • If reverting a snapshot only one snapshot for that database can exist.

Limitations

  • Corrupt databases cannot be reverted.
  • You cannot backup or restore database snapshots.
  • Offline databases or filegroups will prevent a snapshot from being created.
  • Snapshots are not a backup / restore replacement because a bad block on disk will cause a snapshot to be marked suspect.
  • Snapshots are not upgraded as part of the upgrade process therefore the snapshot will not be available after a server is upgraded.

Next Steps

References:

(1) SQL Server 2012 Internals, Microsoft Press



Last Update:


signup button

next tip button



About the author
MSSQLTips author Burt King Burt King is Senior Database Administrator at enservio with more than 15 years experience with SQL Server and has contributed to MSSQLTips.com since 2011.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools