SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume
This article describes a "by design" behavior that occurs when you use VSS to back up files on a volume.
Original product version: SQL Server
Original KB number: 951288
Symptoms
When you use a Volume Shadow Copy Service (VSS) application to backup files on a volume that includes SQL Server database files, SQL Server records a backup operation in the backupset
history table. This behavior occurs even if you did not actually back up the database files of SQL Server.
Cause
This behavior occurs because VSS application calls the SQLWriter service on the system during backup operation. For more information on how VSS applications interact with SQL Writer, review SQL Server Back up Applications - Volume Shadow Copy Service (VSS) and SQL Writer.
Precautions to take if you use the entries in the backupset history table for data recovery
If you want to use entries in the backupset
history table for data recovery, you must verify that the entries represent actual database backup operations.
Verify that an entry represents a native database backup operation (as opposed to a VSS snapshot backup)
To do this, run the following statement:
USE msdb
GO
SELECT server_name, database_name, backup_start_date, is_snapshot, database_backup_lsn
FROM backupset
In the result, notice the database_backup_lsn
column and the is_snapshot
column. An entry that represents a native database backup operation has the following characteristics:
- The value of the
database_backup_lsn
column is not 0. - The value of the
is_snapshot
column is 0.
Verify that the backup set has no errors
To do this, run the following statement:
WITH backupInfo AS( SELECT database_name AS [DatabaseName],
name AS [BackupName], is_damaged AS [BackupStatus],
backup_start_date AS [backupDate],
ROW_NUMBER() OVER(PARTITION BY database_name
ORDER BY backup_start_date DESC) AS BackupIDForDB
FROM msdb..backupset) SELECT DatabaseName
FROM backupinfo WHERE BackupIDForDB = 1 and BackupStatus=1
If this query returns any results, it means that you do not have good database backups after the reported date. We strongly recommend that you perform a full database backup as soon as possible and verify that the full database backup is clean.
The is_damaged property
The backupset
table in the msdb database contains a row for each backup set. The is_damaged
property in the backupset
table indicates whether damage to the database was detected when the backup was created. Therefore, the backup may be damaged and not restorable.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for