Prashanth Jayaram

Understanding Database snapshots vs Database backups in SQL Server

May 4, 2018 by

In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restoration operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Database snapshots are like a view of a database as it was at a certain point in time. It is a read-only copy of the data and the state of the pages, which are made possible using a pointer file called the sparse file. A snapshot contains the original version of pages, and changes in the source since the snapshot was created. However, remember that this is not a full copy of the database.

Snapshots are typically useful for purposes like audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.

It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

Snapshots can be created using the CREATE DATABASE command along with the AS SNAPSHOT option. A snapshot always starts with a near-zero size. This is because a snapshot stores changes to the database since the snapshot was created. As changes in the database occur, the snapshot starts to grow, and may even see significant variation in size. Therefore, it is always recommended to keep an eye on them to avoid low-disk-space alerts.

The database snapshot feature is made available in all editions starting with SQL Server 2016 SP1.

Feature Enterprise Standard Web Express with Advanced Services Express
Database snapshot Yes Yes Yes Yes Yes

Points to note

  1. SQL Server Management Studio does not provide a graphical interface for creating snapshots; the only way to create them is using TSQL commands.
  2. The snapshot file name accepts arbitrary file extensions.
  3. SQL Server does not support backup operations over sparse files. In other words, sparse files cannot be backed up.
  4. A database snapshot appears to never change, because read operations on a database snapshot always accesses the original data pages, regardless of where it resides.
  5. After a page has been updated, a read operation on the snapshot still accesses the original page from the source database, and only the modified pages from the sparse file, also known as the side file.
  6. DBCC commands use an internal reference of database snapshots to validate the required transactional consistency of the database.
  7. When we start DBCC CHECKDB, a hidden database snapshot is created. There is no control, however, over these files, since these are created as alternate streams of the files.
  8. Alternate streams are not used since SQL Server 2014. And the database snapshot is created at the same location as the existing database.
  9. The database snapshots doesn’t reserve any space; the growth of the snapshot is directly proportional to the transaction rates that occur on the source database.
  10. The mechanism of reverting the database snapshot doesn’t work on an offline or a corrupted database. Also, reverting doesn’t work if any of the source files that were online when the database snapshot is created, are offline during reversion.
  11. A database snapshot primarily depends on the side file for each of the data files in the source database. These side files are known as sparse files. Space allocations for these sparse files are made only for the modified portion of the data in the corresponding database file. It doesn’t include an allocation for the remaining portions of the source database—only the changes.
  12. The side page table stores indicator bits which represents data validity and include an in-memory bit map.
  13. During a read operation, a database snapshot always accesses the original data pages, regardless of where it resides. In the example below, the data read operation is performed on pages 1 through 10. However, if pages 3, 6 and 7 are the only ones that have changes, the pages 1, 2, 4, 5, 8, 9, 10 are read from the source database, and pages 3, 6, 7 are read from the sparse files.

Lets deep-dive into the concepts using a demo

The following example creates the database SQLShackDSDemo; the first data file is SQLShackDSDemo_dat and the second file is a log file, SQLShackDSDemo_log.

The next step is to create a table SQLShackAuthorDS and insert dummy records into it.


Let’s now run he is following T-SQL to query the system table called sys.master_files.

We can see that the data file SQLShackDSDemo_Data for the database SQLShackDemo, and the log file for the database, SQLShackDSDemo_log have been created.


In this section, we’re going to create a new snapshot for the database, called SQLShackDSDemo_Snapshot. Next, we’ll specify the logical name of the source data file, SQLShackDSDemo_Data, followed by a snapshot file. In this case, these files are created at F:\PowerSQL, as files called, SQLShackDSDemo_SnapshotData with some arbitrary file extension, snap. Finally, we need the clause AS SNAPSHOT OF, followed by the name of the source database.

We can now see the Snapshot, called SQLShackDSDemo_Snapshot, in the Database Snapshots folder in the Explorer.

Let’s now query the database snapshot


Next, let’s query the system catalog to get the size of the sparse file. To do this, select the is_sparse column from either sys.database_files in the database snapshot or from sys.master_files. This is a boolean flag-like indicator:

  1. 1 = File is a sparse file.
  2. 0 = File is not a sparse file.

Now, perform updates on the source database.


Perform the revert database operation using the snapshot

Let’s see how to revert the accidental updates performed on the source database from the recently taken snapshot

In this example, we revert the changes made on the table, SQLShackAuthorDS, using the snapshot by merging the authorname column based on the id field.

The following output shows that the data is updated

Revert the entire database using restore operation

A database revert operation requires the RESTORE DATABASE permissions on the source database. To revert the database, use the following Transact-SQL statement:

Difference between Database backups vs Database snapshots

Database Backup Database Snapshot
A SQL Server database backup and restore is a means of safeguarding and protecting data in your SQL Server databases A database snapshot is a read-only, static, transitionally consistent view of the source database.
The destination for a SQL Server database backup can be local, remote, network share, or cloud storage A database snapshot must reside on the same server
Can be created using GUI and T-SQL Can be created only using T-SQL
Performance of the system may be impacted only during the backup process. Performance is impacted during snapshot creation as well as restoration due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
Backups can be created at any time. Backups are of various kinds: full, differential, transactional log, etc. Backups are a copy of the entire source (and not just the changes). Restoration can be done to the original location or to a different database or instance. A snapshot is a “point-in-time” copy of a database. For instance, if you made a snapshot of a database at 1 PM today, and made changes to it, and then decided to bring back the database to how it was at 1, you could perform a restore from the 1 PM snapshot. You just have to ensure that the database files are usable.
SQL Server backup depends on physical storage Snapshots depend on the database but not on the underlying subsystems. This is the reason database snapshot cannot be used as a substitute for the backup-and-restore operation.
It’s possible to restore a database with an entirely different name and to a different location. It’s possible to restore the snapshot only to the same source.
Backup and Restore operations on system database is allowed in various scenarios. Snapshots of the system databases—model, master, and tempdb—are prohibited.
The backup and restore operation process works for on any databases. We cannot backup or restore database snapshots.
All the data-pages are to written to the defined storage. The database snapshot technique uses a special file known as a sparse file. It’s a side file of the source database. It holds the pointers to the original source of the data pages.
Backup and restore processed don’t provide for the option to perform object-level restore. Snapshots provides an alternate way to rebuild a dropped table or lost data immediately to a different object name and merge the data back into the source database.
The process of reverting is potentially much faster in some cases than restoring from a backup. It’s determined by the size of the database.

How to decide:

  • Snapshot backups can be used for reporting purpose.
  • Snapshots provide a historical reference for auditing.
  • Reverting the source database to a specific point in time is faster.
  • Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting.
  • Snapshots safeguard data against accidental changes or human errors.
  • Snapshots help with application deployment and release management involving huge data changes or schema modification
  • Snapshots help build a test system and quickly run through the testing process.

Summary

In this article, we have discussed the difference between snapshots and backups in SQL Server, making it easier for an administrator to choose between the two available options. We saw that a snapshot represents a point-in-time reference to the original database.

The two are very different techniques and from a performance perspective, a snapshot is created much faster than a backup/restore, but that is because it does not contain any real data when you first create it. Changes to the data pages are written to sparse file only when there is an update on the source database.

You can create more than one snapshot to source database. However, as the number increases, the I/O also increases, which reduces the system performance.

Database snapshots can only be a supplement for backup/restore, never a substitute.

Snapshots are found to be useful for reporting and auditing purposes, since it generally doesn’t involve locking. It is also useful for some application deployment that involves schema-level changes and large bulk updates.

A database snapshot uses the source database files for all read operations. The performance of the queries directly depends on how busy the source database is, the rate at which the transactions occur on the source database, how many queries are run simultaneously on both the databases, disk IO, hardware performance, etc.

Table of contents

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL databases Using PowerShell and Windows Task Scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


Prashanth Jayaram
Backup and restore

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

168 Views