By: Joe Gavin | Updated: 2024-01-02 | Comments (2) | Related: More > Database Administration
Problem
SQL Server backups have always been the Go-To Approach for DBAs to recover databases and to refresh lower-level environments like development or test servers. One area that most DBAs overlook are the benefits of snapshots for recovery, quickly refreshing an environment, auditing, reporting, and even ransomware protection.
Solution
In this article, we are going to look at the benefits and types of snapshots that can benefit SQL Server and why every DBA should be using snapshots in their environment in addition to database backups.
What is a snapshot?
A snapshot is a static image of data captured at a point in time. As the name implies, it's a picture of the data at the time it's taken and can be created almost instantly. At the time the snapshot is taken, the source data and snapshot are identical. As data changes in the source, the changes are tracked, but the snapshot will remain the same from the point it was issued allowing you to revert back to that prior state if needed.
Types of snapshots
There are two types of snapshots: crash-consistent and application-consistent.
Crash-consistent snapshots
Crash-consistent snapshots are write-order consistent, meaning everything is captured at one time. Only the state of data that's written to disk at the time the crash occurs is captured.
A simple example of crash-consistency is you open a text file in Notepad on a desktop computer that's not plugged into a UPS. Then you make some edits to the file, but don't save them. Someone kicks the power cord out of your computer, and it goes down. You plug it back in, turn it on, and open the file again. The original text before your edits in the file are intact, but your changes are lost.
Taking a crash-consistent snapshot is less of a drain on resources, but is not a perfect recovery as you may lose unwritten data. This is likely fine for file, Active Directory, DHCP, etc. servers, but not for database servers.
Application-consistent snapshots
Application-consistent snapshots are also write-order consistent. In addition to capturing the data that's written to disk, application-consistent snapshots also capture the data that is in memory.
Application-consistent snapshots are needed where there is less tolerance for data loss, such as with database servers. They are more of a resource drain and may take longer than a crash-consistent snapshot to save but are better suited for databases and not losing important data.
How snapshots can help SQL Server DBAs
There are several ways that snapshots could be used and here are a few examples.
Refreshing Databases
Database snapshots are ideal for Development, Test and QA environments. Instead of spending hours restoring a large database, you can quickly revert the source database back to the time and state when the snapshot was taken. This allows testing to be done again at that same beginning point with very little time delay compared to having to issue a complete database restore.
Point in Time Reporting
Snapshots are ideal for static ad-hoc reporting. A snapshot can be taken at any point in time and the data will remain static even while the production database continues to change. This way you can run the same report or query over and over again and get the same results.
Auditing
They are also useful for auditing between points in time. The key is snapshots being point in time views of the data. You can create multiple snapshots and compare the data between snapshots or even the source database.
Recovery from a Database Mistake
Let's say someone runs the wrong DELETE or UPDATE statement or DROPS an object by mistake. One way to fix the mistake is to use what is in the snapshot to help undo the mistake. It may not work in all situations if there is a complex set of changes you need to undo, but this could be a lot faster than doing a database restore and also overwriting any other changes that occurred in the database.
Another option is reverting the database back to the state it was in prior to the mistake by using the snapshot. This would be similar to a point in time restore but could be much faster than doing a full database restore.
Recover from a Hardware or Software Issue
Another scenario is a upgrade goes terribly wrong and leaves your application, or even the Windows OS in a broken state. If you are using virtual machine snapshots, you could roll back the entire VM to the point before the failure which could save a significant amount of time.
Snapshots Compliment Backups, But Do Not Replace Them
It's very important to note that snapshots complement data recovery efforts, but do not replace backups.
In the event the source had data corruption, the snapshot will still be pointing to corrupt data.
If there is a need to do a point in time restore, database backups would give you the option to restore to any point in time, where the snapshot only allows you to revert back to time the snapshot was taken.
If there is a need to make a copy of the database on a different environment then a database backup would be needed to restore to the other server.
Snapshots reside on the same server and if there is a server failure both the source database and the snapshot would not be accessible.
If you need to have a long-term archive of a database, this can only be done with a database backup.
As data changes in the source, the size of the snapshot will grow and at some point the snapshot could be as large as the database therefore requiring increased storage. So, snapshots are useful for short term but not as a long-term option.
Types of snapshots
Snapshots allow fast recovery of databases and VMs and even allow self-service restoration or rollback of files. The types are explained here.
Native SQL Server
SQL Server native snapshots are supported in all SQL Server editions as of 2016. A native snapshot is a read-only static view of the source database at the time the snapshot is issued. They can only exist on the same SQL Server as the source database.
Array Snapshots
Snapshots in the storage array are done by updating the metadata that keeps track of the logical / physical relationship of Logical Unit Number (LUNs) / disk volumes. The mapping changes, but the underlying data does not.
VM Snapshots
VM snapshots are issued at the Hypervisor level and are a snapshot of an entire Virtual Machine's disks and memory. They provide a way to rollback or clone a VM.
File Snapshots
File snapshots are just as the name implies, point in time images of files with a map of file changed. File snapshots are how SharePoint can let you roll back to earlier versions of files.
Benefits of snapshots
There are several technologies from numerous vendors to create and manage snapshots. Storage level snapshots are typically managed by storage admins, VM snapshots are typically managed by VM admins, and database snapshots are managed by DBAs.
Here are a few of the reasons why you should look at implementing snapshots.
Speed
As snapshots are pointers to data at a point in time rather than an actual copy of the data, they are extremely fast to create. In most cases the snapshot can be created almost instantly.
Reduce Storage Needs
Storage needs are significantly reduced using a snapshot compared to a complete copy. When the snapshot is initially taken very little storage space is required to create the snapshot since it relies on the source data. As the source data changes there is a need to track the original state so the snapshot can use this if there is a need to revert. So the more changes that occur in the source the greater storage need there is to track these changes. Therefore if you keep snapshots for a long time there could be a significant need for storage because of all of the possible source changes that occur over time that need to be tracked. It is possible that some point the snapshot could take up as much space as the source.
Faster Than a Database Restore
Reverting a snapshot can be faster than restoring a database due to only having to redirect pointers for things that have changed since the snapshot was taken. As opposed to restoring a database that will require the complete replacement and therefore requiring a lot more IO to occur. The less changes that have occurred since the snapshot was taken the faster the database can be reverted.
Ransomware Protection
Ransomware attacks are low risk and can be highly profitable for the bad guys. Paying the ransom is no guarantee you'll get access to your data back, so you need a way to protect your data.
Here are a few attacks that were in the news:
- Curry County Oregon had to rebuild their entire IT infrastructure after an attack that crippled the count's business and cost millions of taxpayer dollars to recover.
- University of Vermont Medical Center had to turn away cancer patients scheduled for treatment. They were unable to provide lifesaving radiation treatment for a week.
- Harvard Pilgrim Health Care was hit with a ransomware attack and were unable to confirm some patients' prescription coverage.
These are just a few.
To protect yourself from ransomware, taking repeated snapshots can allow you to quickly revert the state of the environment in the event of an attack. By using immutable snapshots, snapshots that cannot be changed or deleted, this can further protect against ransomware and malware attacks by ensuring the snapshot is not impacted by the attack. The ability to revert the snapshots is assured if the snapshot cannot be deleted without a secure process being carried out.
SQL Server Snapshots
One quick way to take advantage of snapshots is to use the database snapshot functionally built into SQL Server.
SQL Server snapshots were first introduced in SQL Server 2016. They are read only and static and can be created in the same SQL Server instance for any user database using simple T-SQL code. When the snapshot is first created a shell file (sparse file) is created that looks like it takes up the same amount of space as the data file, but if you look closely at the file properties you will see that very little space is actually taken.
As the source database changes, the changes are tracked in a sparse file so the data and objects in the snapshot remain looking the same. As more changes occur in the source the space used in the sparse file will grow to keep the database state the same for the snapshot.
SQL Server Management Studio (SSMS) does not have a wizard to create snapshots, so you need to use T-SQL or you could use PowerShell.
Create Snapshot with T-SQL
Snapshots are easily created with T-SQL code as shown below:
/*
snapshot name = AdventureWorks2022_snapshot
logical data file name of source database = AdventureWorks2022
sparse file operating system name = C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022.ss
source database name = AdventureWorks2022
*/
CREATE DATABASE AdventureWorks2022_snapshot ON (
NAME = AdventureWorks2022,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\AdventureWorks2022.ss'
) AS SNAPSHOT OF AdventureWorks2022;
GO
Create Snapshot with PowerShell
There are a few ways a snapshot could be created with PowerShell and in this example we look at also using dbatools to make the creation even simpler.
New-DbaDbSnapshot -SqlInstance MyServer -Database AdventureWorks2022 -Name AdventureWorks2022_snapshot
Either of the above methods will create a snapshot of the AdventureWorks2022 source database called AdventureWorks2022_snapshot as shown below.
T-SQL Snapshot Backups for SQL Server 2022
T-SQL Snapshot Backups are a new feature in SQL Server 2022 and they work in conjunction with backup applications. This is an extremely fast way to create a backup compared to a traditional backup you create with SQL Server. T-SQL Snapshot Backups also eliminates the need for Volume Shadow Copy Service and SQL Writer.
Backup Database as Snapshot
There are basically three steps that are taken to create the snapshot backup:
- First, the database I/O is frozen with an ALTER DATABASE command.
- Second, the snapshot is taken with an application outside of SQL Server.
- Third, a SQL backup command is issued to capture meta data which is written to a bkm file, the snapshot backup is logged in msdb, and the I/O for the database is thawed so it can be used again.
First we suspend database access:
ALTER DATABASE [AdventureWorks2022] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
Second the snapshot is taken using an application outside of SQL Server.
Third we issue the backup command.
BACKUP DATABASE [AdventureWorks2022]
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\AdventureWorks2022.bkm'
WITH METADATA_ONLY, FORMAT;
Restore Database from Snapshot Backup
Here is how to restore the database from the snapshot taken previously which is also very fast.
The snapshot is first mounted by whatever snapshot application you use.
Then the snapshot backup is restored as follows.
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup\AdventureWorks2022.bkm'
WITH METADATA_ONLY;
DBAs, Storage Admins and VM Admins Working Together
As we have seen, snapshots can be created at many different levels. This is why it is important for DBAs, Storage Admins and VM Admins to work together to define and implement the best approach for using snapshots.
At a high level a snapshot could be used to revert a single database, but if there is a more significant need a snapshot of the entire VM could be extremely helpful in the event of a failure or ransomware attack.
Using snapshots will require additional storage space, so this also needs to be factored into the implementation.
There is still the need to do point in time restores of databases, so DBAs need to discuss how this process works with both Storage Admins and VM Admins. In addition, there are several ways that snapshots can be taken out side of SQL Server that DBAs need to take the time to learn about and understand.
The best approach for recovery is to utilize or at least explore all options so make sure you take the time to work together and implement the best strategy for your environment.
Why Snapshots Should be Adopted
There is always resistance to anything new or different, especially in the world of technology. We went from dumb terminals with serial connections to PCs to networked PCs, from physical servers to Virtual Machines, from on-premises hardware to the cloud. It's a natural progression that ideas that were once new are now mainstream. This applies to snapshots as well.
This technology was built for a specific purpose, is readily available and in some cases completely free since you already paid for the technology.
Take the time to better understand how snapshots can be utilized so you have the best possible solution to protect your data assets.
Next Steps
These links contain more information on snapshots.
- SQL Database Snapshots for Reporting, Auditing and Data Recovery
- SQL Server Database Snapshots for Data Comparison
- SQL Server File Snapshot Backups
- How to Revert a Database Snapshot to recover a SQL Server Database
- Restore SQL Server Databases Using File Snapshot Backups
- Dynamic Snapshots in SQL Server Merge Replication
- Transactional Replication Snapshot Issues in SQL Server
- Changing SQL Server's Default Snapshot Folder for Replication
- Using Snapshot Replication with an Azure SQL Database
- Limit Snapshot Size When Adding New Article to SQL Server Replication
- Overview of Virtual Machine Snapshots in vSphere
- Microsoft SQL Server - How do snapshots make your life easier?
- Database snapshots (SQL Server)
- What are the common challenges and pitfalls of using VMWare snapshots and backups?
- Snapshots vs. Backups—Why You Need Both
- Storage 101: Snapshots vs backup
About the author
Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-01-02
