You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.
Cause
This problem occurs because VSS calls the SQLWriter service or the Microsoft SQL Server Desktop Engine (MSDE) writer.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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.

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.

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 msdbGOSELECT server_name, database_name, backup_start_date, is_snapshot, database_backup_lsnFROM 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:
  • 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 the 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.

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
System Center AdvisorSQL Server Database Backupset is marked as DamagedIn this SQL Server instance advisor detected the presence of one or more backupset marked as "damaged" in MSDB database. This means that the Backup file is damaged and essentially the database is exposed and the backup potentially non-restorable. Review the Information Collected section and ensure that a subsequent good backup is available for the database in question. Refer to the KB article for more details.SQL Server 2008
SQL Server 2008 R2
SQL Server 2012


VSS SQL writer msdb ntbackup
References
For more information about the SQL Writer service, visit the following Microsoft Developer Network (MSDN) Web site: For more information about the backupset history table, visit the following MSDN Web site:
Properties

Article ID: 951288 - Last Review: 03/28/2013 19:11:00 - Revision: 2.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 Workgroup Edition, Microsoft SQL Server 2000 Developer Edition, Microsoft SQL Server 2000 Enterprise Edition

  • kbexpertiseadvanced kbtshoot kbprb KB951288
Feedback
one;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">