Problem
Sometimes there is need to have a static set of data from your database whether that is for reporting, auditing or even recovery of data. This can be pretty easily done using SQL Server backups and restores, but the time that it takes to perform the backup and restore task can be quite long depending on the size of the database. If the need is to only periodically create a static set of data, then this approach will probably work for you. But what if you need to create a static set of data every day or if you need to do it multiple times a day for some type of reporting or auditing. To handle this approach you could do full and transaction log backups and restores, but there is still a lot of overhead to run these processes. In this article we look at how to setup a database snapshot.
Solution
SQL Server has a feature called database snapshots. A database snapshot is a read only copy of your database, but not exactly a copy. If it were a complete copy, then it would be the same as doing a backup and restore and offer no advantage. The way database snapshots work is that a shell of your database file is created instead of a duplicate set of data. When data is read from the snapshot, it is actually being read from the primary database files. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file, so this way the data stays static. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed. Since the initial snapshot is reading from your primary database, the creation of a database snapshot is immediate.
The image below shows when data changes in the primary file a copy of the original data is moved to a sparse file. So when a SELECT is issued against the snapshot database part of the data is read from the primary data file and the changed data is read from the sparse file, so a static set of data is retrieved based on when the snapshot was taken.
Source: SQL Server Books Online
As time goes by, the more the data changes in the primary database, the more data there will be in the sparse files. So you can see if the data is changing quite frequently in your primary database the data in the sparse files can get very large. So understanding how your data changes in your database is extremely helpful as well as how long you keep snapshots of your database.
Source: SQL Server Books Online
Creating a SQL Server Database Snapshot
Creating and using database snapshots is very straightforward. Let’s say we want to create a database snapshot for the Northwind database.
First let’s get a list of data files from the database we are trying to create a snapshot for.
sp_helpdb 'Northwind'
We get the following output:
So from the above we need to get the data file name which is “Northwind” where fileid = 1.
To create a database snapshot you use a T-SQL command as follows.
- CREATE DATABASE – we specify the name of the snapshot database
- NAME – use “Northwind” which we got from the sp_helpdb output above
- FILENAME -specify the location to create the sparse file
CREATE DATABASE Northwind_Snap
ON (NAME = Northwind,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Northwind_Snap_data.ss' )
AS SNAPSHOT OF Northwind
Using the SQL Server Database Snapshot
Using the snapshot is just like using any other database. In SSMS, under Database Snapshots, we can see the snapshot we created called Northwind_Snap and a list of the tables.
You connect to the database and execute your SELECT queries. The thing to remember is that this is a read-only copy, so you can not do updates.
Deleting a SQL Server Database Snapshot
Getting rid of a snapshot is just as easy, you can issue a DROP DATABASE command or delete the database snapshot using SQL Server Management Studio.
Multiple Database Snapshots
In addition, you can have multiple snapshots of your database. Each time a snapshot is taken you create a static set of data as it looked at that point in time.
Next Steps
- If you have a need for creating a static set of data for reporting, auditing or data recovery take a look at using database snapshots
- Run some tests to make sure that performance does not suffer by using database snapshots
