SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume

View products that this article applies to.

SYMPTOMS

Consider the following scenario. You install Microsoft SQL Server on a volume. You use a Volume Shadow Copy Service (VSS) application to back up files on the volume. In this scenario, SQL Server records a backup operation in the backupset history table. This problem occurs even if you did not actually back up the database files of SQL Server.

Note The NTBackup.exe utility is a VSS application.

Back to the top

CAUSE

This problem occurs because VSS calls the SQLWriter service or the Microsoft SQL Server Desktop Engine (MSDE) writer.

Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top

MORE INFORMATION

Description of the MSDE writer and the SQLWriter service

The MSDE writer is included in the VSS framework in Windows XP and in Windows Server 2003. VSS uses the MSDE writer to back up SQL Server 2000 databases. The SQLWriter service is installed when you install SQL Server 2005. VSS uses the SQLWriter service to back up both SQL Server 2005 databases and SQL Server 2000 databases.

Back to the top

How VSS performs a backup operation

When VSS performs a backup operation on a volume, VSS calls all writers that have data on the volume. This makes sure that the backup is valid. When VSS calls the SQLWriter service or the MSDE writer, the SQLWriter service or the MSDE writer inserts a record into the backupset history table. This record shows that a backup operation was performed. However, the SQLWriter service or the MSDE writer cannot determine whether the files that VSS backed up are database files.

Back to the top

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.

How to verify that an entry represents an actual database backup operation

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 an actual database backup operation has the following characteristics: