By: Burt King | Updated: 2015-04-27 | Comments | Related: > Upgrades and Migrations
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.
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.
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.
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.
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
- Implementing database snapshots is remarkably simple. Successfully using them requires reading up on the Microsoft documentation to become familiar with the requirements as well as the limitations.
- Check out these resources:
- Read the Microsoft knowledge base article on database snapshots.
References:
(1) SQL Server 2012 Internals, Microsoft Press
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-04-27
