Restoring Data with Database Snapshots
Snapshots use a copy-on-write operation that minimizes the drive space required for each snapshot. Unless there are changes in the original database, the snapshots will remain empty. Queries to the snapshot for unchanged records will automatically be redirected to the original database. Queries to the snapshot for modified records will show the data as it existed at the time of the snapshot’s creation. Modified records are copied from the original database before they are changed. The copy-on-write process copies data at the page level, so some unchanged records will likely end up in the snapshot.
What DBA wouldn’t appreciate a process that allows for fast and easy restoration of lost data? If the process allows you and your users to examine the information before restoring it, to use a minimal amount of drive space, and to carry out all the necessary steps with only local resources, then you have a solution that not only saves you time, but improves the options you have for supporting users and developers.
All of these options are available when using SQL Server’s Database Snapshot functionality. When configured, it gives you access to a point-in-time, read-only view of the database and its records. Multiple snapshots can be created on the same database. Database snapshots require the original database to function, and you will be required to delete them before you can remove the original database.
How To Use Database Snapshots
Database Snapshots require the Enterprise Edition of SQL Server and can only be created using T-SQL. The steps below show you how to do this on a test server. You will need the Enterprise or Developer editions of SQL Server 2005 or 2008. To use the scripts provided, create two folders on the root of the C: drive named Database and Snapshot.
- Create the test database.
- Create a snapshot
- Change records in original database, which was created in step1
- Revert to the original version of the table
- Revert to the original database
Step 1 – Create the test database
Use the script in below to create a database named DB1 and a table named dbo.Contacts with five (5) records.
Create Database DB1
Create Table dbo.Contacts
(ID nchar(5) NOT NULL,
Constraint PK_Contacts Primary Key Clustered (ID Asc))
/* Insert 5 records into the Contacts Table */
Insert DB1.dbo.Contacts Values(‘101′,’John’,’Harrison’)
Insert DB1.dbo.Contacts Values(‘102′,’Jessica’,’Forthwright’)
Insert DB1.dbo.Contacts Values(‘103′,’Earl’,’Russell’)
Insert DB1.dbo.Contacts Values(‘104′,’Stanley’,’McDonald’)
Insert DB1.dbo.Contacts Values(‘105′,’Mary’,’Kellerman’)
Step 2 – Create a snapshot
Use the below script to create a snapshot of the database. After it is created, verify that the snapshot file was created (C:\Snapshot\DB1_Snapshot.ss). Notice that no log file is needed. This is a read-only version of DB1 as it existed at the time it was created. In the Figure 2 script, notice that the NAME parameter points to the actual name of the database file and not the name of the database as specified in the AS SNAPSHOT OF parameter. Databases with multiple data files would, therefore, need multiple NAME parameters, one for each file.
/* Create a snapshot of the DB1 database */
CREATE DATABASE DB1_Snapshot ON
( NAME = DB1, FILENAME = ‘C:\Snapshot\DB1_Snapshot.ss’)
AS SNAPSHOT OF DB1;
Step 3 – Change records in original database, which was created in step1
Modify any record from the Contacts table in the DB1 database. Verify that the information in the DB1_Snapshot database is unchanged, using the script provided in Figure 3. The changes to DB1 have caused the original version of the data pages changed to be written to the DB1_Snapshot.ss file.
/* Verify that the information in the
snapshot and the original database are the same. */
Select * From DB1.dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
/* Update the records in the Contacts table and compare
it to the Snapshot version again */
that the information in the
snapshot and the original database are different. */
Select * From DB1.dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
Step 4 – Revert to the original version of the table
You can restore individual records in a table from the snapshot. Use the script in Figure 4 to see how to fix incorrect deletions or updates.
Step 5 – Revert to the original database
As long as the original database is still intact and online, you can restore all changes made since the snapshot. These database snapshot restores cannot be done when more than one snapshot exists. As with a normal database restore, exclusive access to the database is needed during this operation. To test this process, use Figure 3 script to create differences between both databases, then use Figure 5 to restore the entire database from the snapshot.
Caveats when Using Database Snapshots
Like any other useful DBA tool, database snapshots can be overused or misused. Here are a few precautions to keep in mind before implementing this functionality in a production environment.
Backups are still required:
There is no substitute for regular, tested backups that are stored in a remote, secure location. The database snapshot requirement that the files be stored on the same server as the original database prevents them from being used in this manner. The fact that the snapshots become obsolete if the original database is lost also limits their use for this purpose. The strength of snapshots is in their ability to provide point-in-time views of your data and ad-hoc restore options.
Beware of too many snapshots:
Although the empty state of a new snapshot file makes their size negligible, take care when creating multiple snapshots on the same database. Theoretically, a database with 10 GB of data and 24 snapshots can suddenly and unexpectedly require up to 250 GB of drive space. The ability to quickly restore a database to how it looked at a particular hour of the day is very convenient, but if this functionality is not needed, the additional resource requirements can tax a mission-critical server beyond acceptable levels. A snapshot should only be created to meet specific administrative needs and then deleted when it is no longer necessary.
Using Snapshots for Read-only Operations:
The point-in-time, read-only nature of snapshots makes them a candidate for reporting or similar solutions. Using them in this way, however, limits their use as an ad-hoc restoration solution. Before allowing others to use your snapshots for development or business solutions, consider how this will affect the options available to you when administering your databases. Other dedicated read-only solutions, like Log Shipping might be considered.
If you decide to take advantage of Database Snapshots, keep its limitations in mind. It cannot replace your backup solution, and you must carefully consider the additional resource demands of each additional snapshot. It can, however, provide a fast and simple solution for viewing point-in-time data and restoring lost information from databases. Overall, it’s a useful tool for reducing downtime and improving the availability of your databases.