Troubleshooting SQL Server Backup History: Understanding and Managing Backupset Operations

Table of Contents

When managing SQL Server databases, understanding the backup history is paramount for successful disaster recovery. SQL Server meticulously logs backup operations in the msdb database, specifically within the backupset table. This table serves as a crucial record of when backups occurred, what databases were backed up, the backup type, and other vital metadata. However, interactions with Volume Shadow Copy Service (VSS) applications can sometimes lead to entries in this history that might appear confusing at first glance, potentially impacting recovery planning if not properly understood.

This article delves into a specific behavior where VSS backup applications trigger entries in the SQL Server backupset history table, even when the VSS process is capturing a volume snapshot that merely includes SQL Server database files, rather than performing a direct database backup initiated through SQL Server management tools or T-SQL commands. Understanding this behavior is key to accurately interpreting your backup history and ensuring reliable recovery strategies.

The Interaction Between VSS and SQL Writer¶

The Volume Shadow Copy Service (VSS) is a Windows service that creates point-in-time copies (snapshots) of volumes. These snapshots allow backup applications to back up files while they are in use, including actively used database files. When a VSS application initiates a snapshot operation on a volume containing SQL Server database files, it communicates with SQL Server through the SQL Server VSS Writer service (SQLWriter).

The VSS process involves several steps, and SQLWriter plays a critical role in ensuring the transactional consistency of SQL Server databases within the snapshot. When a VSS application (the Requester) requests a snapshot:

1. The VSS Service notifies registered Writers, including SQLWriter.
2. SQLWriter prepares SQL Server databases for the snapshot. This involves flushing pending transactions to disk and briefly holding writes (a “freeze” state) to achieve a consistent state for the snapshot.
3. The VSS Provider creates the shadow copy (snapshot) of the volume.
4. SQLWriter is notified that the snapshot is complete and can then “thaw” the databases, allowing normal write operations to resume.
5. SQLWriter reports metadata about the SQL Server components to the Requester via the VSS Service.

During this interaction, specifically during the metadata reporting phase or just before the thaw, SQLWriter logs an event in the msdb.dbo.backupset table to record that a VSS snapshot involving SQL Server was taken. This log entry serves as an informational record indicating that the state of the SQL Server databases at the time of the snapshot was captured consistently by the VSS process.

While this VSS operation captures the state of the database files at a point in time, it is fundamentally different from a native SQL Server database backup created using commands like BACKUP DATABASE. Native backups read the database pages directly and write them into a backup file (.bak). VSS snapshots, conversely, capture the entire volume state, and the backup application then backs up the files from the snapshot.

Deciphering Entries in the backupset Table¶

The backupset table in the msdb database is the central repository for information about all backup and restore operations performed by SQL Server or initiated via SQLWriter. Understanding the key columns is essential for distinguishing native database backups from VSS snapshots recorded by SQLWriter.

Here are some relevant columns in the backupset table:

• server_name: The name of the server where the backup occurred.
• database_name: The name of the database involved in the operation.
• backup_start_date: The date and time when the backup or snapshot operation started.
• backup_finish_date: The date and time when the operation finished.
• type: The type of backup (D = Database, L = Log, I = Differential, F = File, G = Differential File, P = Partial, Q = Differential Partial). For VSS snapshots recorded by SQLWriter, this is often ‘D’ as it captures the full database state.
• is_snapshot: A bit flag indicating if the backup was a snapshot backup (1 = Yes, 0 = No). This is the primary indicator for VSS entries.
• database_backup_lsn: The log sequence number (LSN) of the database checkpoint at the start of the backup. For full and differential native backups, this is a non-zero value. For VSS snapshots recorded by SQLWriter, this value is typically 0.
• first_lsn: The LSN of the first log record included in the backup.
• last_lsn: The LSN of the last log record included in the backup.
• checkpoint_lsn: The LSN of the most recent checkpoint at the time of the backup.
• is_copy_only: A bit flag indicating if the backup was a copy-only backup (1 = Yes, 0 = No). VSS backups recorded by SQLWriter are often marked as copy-only, as they don’t break the regular backup chain.
• is_damaged: A bit flag indicating if the backup set is damaged or corrupted (1 = Yes, 0 = No). This column is crucial for verifying backup integrity.

By querying this table, you can retrieve information about all recorded operations.

Identifying VSS Snapshot Entries¶

As mentioned, entries resulting from VSS snapshot operations initiated by an external application (like Volume Shadow Copy Service) but involving SQL Server via SQLWriter have distinct characteristics in the backupset table compared to native SQL Server backups.

To differentiate between a native SQL Server database backup and a VSS snapshot logged by SQLWriter, you can examine the is_snapshot and database_backup_lsn columns.

A typical query to retrieve relevant backup history information is:

USE msdb;
GO

SELECT
    server_name,
    database_name,
    backup_start_date,
    type,             -- Added to see backup type (usually 'D' for VSS)
    is_snapshot,
    database_backup_lsn,
    is_copy_only      -- Added to see if marked copy-only
FROM backupset
ORDER BY database_name, backup_start_date DESC;

In the result set:

• An entry representing a native SQL Server database backup initiated via SQL Server Management Studio, T-SQL BACKUP DATABASE command, maintenance plan, etc., will typically have:

  • is_snapshot value of 0.
  • database_backup_lsn value that is non-zero. This LSN represents the state of the database log at the time the backup started.

• An entry representing a VSS snapshot operation involving SQL Server database files, as logged by SQLWriter, will typically have:

  • is_snapshot value of 1.
  • database_backup_lsn value of 0. This is a key indicator that it was a VSS snapshot and not a native backup.

VSS entries might also be marked as is_copy_only = 1. This is because VSS snapshots typically do not interact with the native SQL Server backup chain (full, differential, log backups) and therefore don’t affect subsequent differential or log backups.

Understanding this distinction is vital. While a VSS snapshot provides a consistent point-in-time copy of the database files, restoring only from this snapshot usually requires using the VSS-aware backup application that created the snapshot, not SQL Server’s native restore functionality. Relying solely on the backupset entry for a VSS snapshot using RESTORE DATABASE commands in SQL Server will fail because the entry doesn’t correspond to a .bak file created by SQL Server’s native engine.

Verifying Backup Set Integrity: The is_damaged Property¶

Beyond identifying the type of backup (native vs. VSS), it is equally important to verify the integrity of the backup sets recorded in the history. The is_damaged column in the backupset table indicates whether SQL Server detected any corruption or damage within the backup header or the backup media itself when the backup was created.

A value of is_damaged = 1 in a backupset entry indicates that the backup operation reported encountering corrupt pages or other issues during the backup process. Relying on a damaged backup set for recovery is risky, as it may not be restorable or may contain corrupted data.

To check for potentially damaged backup sets in your history, you can use a query like the following:

USE msdb;
GO

WITH backupInfo AS (
    SELECT
        database_name AS [DatabaseName],
        name AS [BackupName],         -- Internal name of the backup set
        is_damaged AS [BackupStatus],
        backup_start_date AS [backupDate],
        -- Assign a row number to each backup within a database, ordered by date descending
        ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_start_date DESC) AS BackupIDForDB
    FROM msdb..backupset
)
-- Select databases where the most recent backup (BackupIDForDB = 1) is marked as damaged
SELECT
    DatabaseName,
    BackupName,
    backupDate
FROM backupInfo
WHERE BackupIDForDB = 1
AND BackupStatus = 1;

This query specifically looks for the most recent backup entry (BackupIDForDB = 1) for each database that is marked as is_damaged = 1. If this query returns any rows, it means the latest recorded backup for those databases reported issues.

It is crucial to note that the is_damaged flag is set at the time of the backup. It reflects issues encountered during the backup operation itself. It does not automatically detect damage that might occur to the backup file after the backup is completed (e.g., storage corruption). To verify the integrity of the backup file on storage, you should use the RESTORE VERIFYONLY command.

For example:

RESTORE VERIFYONLY
FROM DISK = N'C:\SQLBackups\YourDatabase_Full_20231027.bak';
GO

Running RESTORE VERIFYONLY is the most reliable way to confirm that a backup file is readable and appears structurally sound for a potential restore operation. Incorporating RESTORE VERIFYONLY into your backup validation process, perhaps via SQL Server Agent jobs or maintenance plans, is a strongly recommended best practice.

If the query for is_damaged = 1 returns results, or if RESTORE VERIFYONLY fails, it indicates a problem with your recent backup strategy. You should:

1. Investigate the cause of the damage (e.g., disk issues, memory problems, software bugs).
2. Immediately perform a new, full database backup for the affected databases.
3. Verify the integrity of this new backup using RESTORE VERIFYONLY.
4. Ensure that this new, valid backup is stored safely on reliable media.

Relying on a damaged backup, whether native or a problematic VSS snapshot entry, can lead to significant data loss in a recovery scenario.

Practical Considerations for Backup and Recovery¶

Managing SQL Server backups in environments utilizing VSS applications requires careful consideration to avoid confusion and ensure recoverability.

1. Understand Your Backup Strategy:

Clearly define which backups are considered primary for disaster recovery. Are you relying on native SQL Server backups (.bak files) managed through SQL Server itself, or are you relying on volume-level VSS backups managed by an external backup application? In many enterprise environments, both might be used, serving different purposes (e.g., native for point-in-time recovery, VSS for bare-metal restores or rapid file recovery).

2. Interpret backupset Entries Accurately:

Regularly review your msdb.dbo.backupset table. Pay close attention to the is_snapshot and database_backup_lsn columns to distinguish VSS entries from native backups. Educate anyone responsible for monitoring or recovering SQL Server about these distinctions. Do not assume every entry in backupset with type = 'D' represents a restorable .bak file created by BACKUP DATABASE.

3. Verify VSS Backups Independently:

If you are relying on VSS backups for SQL Server recovery, ensure your VSS-aware backup application has its own mechanisms for verifying the integrity of the captured data. Check the logs and reports generated by the VSS application for successful completion and any reported errors related to SQL Server or the VSS process. A successful backupset entry with is_snapshot = 1 only means SQLWriter participated successfully; it doesn’t guarantee the external backup application captured and stored the data correctly.

4. Maintain a Native Backup Strategy (Recommended):

Even if using VSS backups, maintaining a separate native SQL Server backup strategy is often recommended. Native backups provide greater flexibility for point-in-time recovery (especially with transaction log backups) and are managed entirely within the SQL Server ecosystem, simplifying troubleshooting compared to integrating with external VSS applications. These native backups should be validated regularly using RESTORE VERIFYONLY.

5. Test Your Recovery Process:

The most critical step is regularly testing your recovery process. Practice restoring databases from both native backups and, if applicable, VSS backups managed by your external application. This ensures you understand the steps involved, verifies that your backups are valid, and confirms that you can meet your Recovery Time Objective (RTO) and Recovery Point Objective (RPO). Restoring to a non-production environment is essential for this testing.

6. Monitor is_damaged and Other Backup Errors:

Actively monitor the backupset table for is_damaged = 1 entries. Configure alerts for failed backup jobs, including both native SQL Server jobs and any VSS application logs that report SQLWriter or VSS-related errors. Proactive monitoring allows you to detect and address backup issues before a disaster strikes.

Troubleshooting Common Issues¶

• Confusing VSS entries with native backups: This is the primary issue discussed. Remember to check is_snapshot and database_backup_lsn.
• Failed VSS snapshots impacting SQL Server: While SQLWriter aims for minimal impact, VSS issues (like storage problems, lack of space for shadow copy storage, or VSS service errors) can potentially cause temporary I/O pauses or errors in SQL Server. Monitor Windows Event Logs (Application and System) and SQL Server Error Logs for related messages during VSS operations.
• SQLWriter service not running: If the SQL Server VSS Writer service is stopped or disabled, VSS applications will not be able to create consistent snapshots of SQL Server databases. This will likely cause VSS backups involving SQL Server files to fail or result in inconsistent backups, and no is_snapshot = 1 entries will be logged in backupset by SQLWriter. Ensure this service is running and set to automatic start.
• is_damaged flag is set: As discussed, this indicates a potential problem with the backup itself. Follow the steps outlined above: investigate the cause, perform a new backup, and verify it. Do not rely on the damaged backup.

By understanding the nuances of how VSS interacts with SQL Server and how these interactions are logged in the backupset table, database administrators can maintain a clear and accurate view of their backup landscape, significantly improving their ability to perform successful data recovery when needed. This knowledge is a fundamental part of a robust database administration practice.

---

We hope this detailed explanation helps you better understand and manage your SQL Server backup history, especially when VSS snapshot operations are involved. How do you currently manage and verify your SQL Server backups? Are there specific challenges you face with VSS interactions? Share your experiences and questions in the comments below!