As a DBA, one of the most critical aspects of managing SQL Server is ensuring the integrity and reliability of database backups. SQL Server’s native backup functionalities offer robust tools for securing your data, particularly for databases utilizing the full recovery model. However, mixing SQL Server native backups with disk or VM snapshots can lead to issues while restoring. It can be like trying to mix oil and water.

In this post, we’ll review why mixing these two backup types can pose risks and how to mitigate these challenges. Here’s what we’ll cover:

• Understanding SQL Server Native Backups – Full, Differential, Log
• Snapshot Backups and SQL Writer
• Impact of snapshot backups on native backups
• Conclusion

Understanding SQL Server Native Backups

We generally recommend using a combination of native SQL Server backups. This can vary a bit depending on a client’s RPO and RTO requirements. Let’s start with the full backup as it serves as the foundation for any backup strategy, establishing a baseline state of the database.

Full Backup

A full database backup creates a complete copy of the entire database at a specific point in time. This type of backup captures the state of the database, including all the data files (mdf and ndf), log files (ldf), and part of the transaction log necessary to ensure the database’s consistency. The main purpose of a full database backup is to provide a starting point for database recovery, allowing the restoration of the database to its state at the time of the backup.

How it Works

When a full database backup is initiated, SQL Server begins by writing a portion of the transaction log to ensure that the backup reflects a consistent state of the database. This means that the backup will include all committed transactions up to the point at which the backup was taken. Uncommitted transactions are not included in the backup, but the data necessary to roll back these transactions is and ensures the database can be restored to a consistent state.

Full backups are straightforward to manage. They do not require additional log or differential backups to restore the database to the specific point in time. However, they can be large and time-consuming. Depending on the database size and available storage, full backups might not be feasible to perform very frequently. This can influence the recovery point object.

We recommend a full backup at least once per week. Next, we move on to nightly differential backups.

Differential Backup

Differential backups only include the data that has changed since the last full backup. Instead of backing up the entire database every time, a differential backup captures only the modifications, making it a faster and more storage-efficient way to maintain up-to-date backup copies of your data.

How it Works

The starting point for differential backups is the last full backup. This full backup serves as the baseline from which all changes are tracked. Tracking is done by marking extents in the database as changed whenever data is modified. Differential backup operations rely on a bitmap page that contains a bit for every extent. Each extent updated since the last full backup is set to 1 in the bitmap.

Subsequent differential backups will also contain all changes since the last full backup, not since the previous differential backup.

This is extremely important when considering a mix of SQL Server native backups and VM or disk based snapshots. Snapshots, using volume shadow copy services and the SQL writer are full backups and will reset the starting point for differentials which occur after them (unless specified to use the COPY ONLY option). We’ll see this scenario later in the demo.

The main advantage of differential backups is efficiency. They are typically much quicker to complete than full backups because they involve less data. They also require less storage space. However, they do add to the complexity of database restores. You first restore the most recent full backup without recovery and then restore the latest differential. Or the differential that occurred closest to the point in time you need to recover to. You do not need to apply each differential backup sequentially.

Let’s look at log backups next. A good starting point, again dependent on your RPO, is to perform a log backup every 15 minutes.

Log Backup

To perform a log backup, the database must be using the full or bulk-logged recovery model. These backups allow for point-in-time recovery of the database, providing a high level of data protection by ensuring that all transactions are recoverable. They capture all transaction log records since the last log backup. The transaction log is a serial record of all changes made to the database, including both data modifications, and enables rollbacks of those modifications if transactions are not completed successfully.

How it Works

SQL Server continuously records all database transactions in the transaction log file. This log includes every insert, update, delete, and schema change operation. Regular log backups are essential for truncating the transaction log file. Truncation does not reduce the file size but marks the space as reusable for new transactions. Without log backups, the transaction log can grow indefinitely, consuming disk space and potentially impacting database availability and performance. The database must be in full recovery or bulk-logged recovery. Full being the most prominent.

To restore you’ll begin by restoring the last full backup (without recovery), the last differential (without recovery) if part of your backup strategy, and then all log backups up to a specific point in time. This is a sequential process requiring all log backups to be applied in order. If one is missing then the restore will fail and you’ll be limited to the point in time just before the break.

It’s important to note some 3rd party VM snapshot solutions include an option to truncate the log after taking a snapshot backup. They do this by initiating a “BACKUP LOG DatabaseName TO DISK = ‘NUL’” command. This will break your transaction log backup sequence and impact your RPO.

Snapshot Backups and the SQL Writer Service

Snapshot backups, using volume shadow copy service and SQL writer, are extremely fast. VSS is a Windows-based technology that allows backup applications to safely back up locked and open files. It’s particularly useful for creating snapshots of databases and other applications that continuously run and handle data.

VSS operates at the file system level and can create a snapshot – a read-only, point-in-time copy of data in seconds. This snapshot technology ensures that files are in a consistent state when backed up.

SQL Server VSS Writer is a Windows service that uses the VSS framework to facilitate creating backups and restores for SQL Server databases. When a VSS-based backup is initiated, the SQL Server VSS Writer ensures that database files are in a transactionally consistent state, making them suitable for backup purposes. The writer is aware of SQL Server’s data files and log files and can freeze and thaw database operations intelligently to ensure data consistency without shutting down the database. You’ll see these operations reported in the SQL Server error log as “I/O is frozen…” events. Log backups are not supported (see here for additional details).

How They Work Together

1. When a snapshot backup is requested, the backup application communicates with the VSS to initiate the backup process. Snapshots that do not use the COPY ONLY option will reset the starting point for your native differentials.
2. VSS communicates with the SQL Server VSS Writer to prepare the databases for a consistent snapshot. This involves ensuring that all transactions are complete and that the databases are in a stable state. I/O is paused.
3. Once the databases are prepared, VSS instructs the storage system to create a snapshot. This process is usually quick and efficient. This does not involve copying data but rather capturing the state of the data at a specific point in time.
4. After the snapshot is taken, the SQL Server VSS Writer allows SQL Server to resume its normal operations, minimizing downtime and impact on the system. However, this can vary if there’s a large number of databases.
5. The snapshot can then be used to create a backup copy of the database files without affecting the running database(s).

Impact of snapshot backups on native backups

I’ve mentioned the impact snapshot backups can have on native SQL Server backups a few times. Especially when using a mix of full, differential, and log backups as recovery relies on a carefully orchestrated sequence. Let’s see this in practice. I’m going to use a tool built into Windows, called DISKSHADOW, which will mimic a backup application to create a VSS snapshot. Here’s the breakdown (please don’t do this in production).

Test Environment:

OS Version	SQL Server Edition and Version	Test database name
Windows Server 2022	SQL Server 2022 Developer Edition	BackupDemo

The demo database is using the full recovery model.

Step 1: Create a native full backup of BackupDemo.

Step 2: Create a native differential backup of BackupDemo.

Step 3: Create a snapshot backup using DISKSHADOW.

Step 4: Open notepad and add the following. In this demo the BackupDemo database files reside on F and G. Save the file as snapshot.txt in the C:\backup folder.

#Diskshadow script file
writer verify "SqlServerWriter"
set metadata C:\Backup\diskshadow_c_f_g.cab
begin backup
add volume F: alias SQLVMDATA1
add volume G: alias SQLVMLOG
create
end backup
#End of script

Step 5: Open a command prompt as an administrator.

Step 6: Run the following.

cd C:\Backup
diskshadow -s C:\Backup\snapshot.txt

Step 7: Once complete, run a second differential backup.

Step 8: Attempt a database restore using the native full backup and the second differential.

RESTORE DATABASE BackupDemo
FROM DISK = 'G:\BACKUP\SQL1\BackupDemo\FULL\SQL1_BackupDemo_FULL_20240306_104745.bak'
WITH NORECOVERY, REPLACE, STATS = 10

RESTORE DATABASE BackupDemo
FROM DISK = 'G:\BACKUP\SQL1\BackupDemo\DIFF\SQL1_BackupDemo_DIFF_20240306_114303.bak'
WITH RECOVERY, STATS = 10

The error below will be encountered due to the snapshot backup occurring between the native full backup and the last differential.

Msg 3136, Level 16, State 1, Line 44
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Msg 3013, Level 16, State 1, Line 44
RESTORE DATABASE is terminating abnormally.

Conclusion

Mixing SQL Server native backups and snapshot backups in an environment isn’t ideal due to the complexities it introduces.

If your organization is using VM or disk snapshots be sure those can meet recovery point objectives. Document and test the restore process using the backup application.

If both types of backups are required, use the COPY ONLY option for all snapshot-based backups and make sure the backup application is not set to truncate transaction logs.

Want to know more?

Here are some additional posts that may be helpful.

• How to Align Your SQL Server to Your RPO and RTO Goals
• What is a SQL Server Recovery Model?
• Using VM Snapshots to Backup SQL Server?
• Where to Start with Disaster Recovery in SQL Server

If you’re concerned about your backup strategy, or more to the point, your ability to restore, contact us. We’re happy to help.