Backing up a SQL Server database using a VSS backup application may fail for some databases
This article helps you resolve the problem that occurs when you use Volume Shadow Copy Services (VSS) based applications to back up your SQL Server databases.
Original product version: SQL Server
Original KB number: 2014054
When you use Volume Shadow Copy Services (VSS) based applications to back up your SQL Server databases, the backup operation may fail if the database name contains either leading or trailing spaces or non-printable characters.
The same problem may also occur when you try to back up a volume that contains one of these databases.
VSS based backup applications use
SQLServerWriter (SQLWriter) to query Writer Metadata Component information to determine which database files need to be backed up. The Writer Metadata Component is created by SQLWriter using VSS API and contains three sets of data:
- Writer identification and classification information
- Writer-level specifications
- Component data
When a database name contains leading or trailing spaces or non-printable characters, this document may not be created successfully. As a result, VSS-based applications will not be able to back up these databases or any volumes that contain these databases.
Rename all databases that contain either leading or trailing spaces or unprintable characters in their names. You can use the following query to locate the presence of such characters in front or at end of the names:
SELECT database_id AS DatabaseID, '##'+name+'##' AS DatabaseName FROM sys.databases
DatabaseID DatabaseName 8 ##AdventureWorks## -- DB name is fine 15 ## DBWithLeadingSpace## -- DB name contains leading spaces 17 ##DBWithTrailingSpace ## -- DB name contains trailing spaces
In the preceding query, if the database name contains unprintable characters they may either be printed out as spaces or some junk.
Writers (like SQL Writer) add components using IVssCreateWriterMetadata::AddComponent, specifying the following component information:
- Logical path (if any)
- Supported feature
- Metadata to be used by the writer during restore
- Display information
- Notification information
C (Volume Shadow Copy Service) are collections of files that form a logical unit for purposes of backup and restore. All files in a component (except those explicitly excluded) must be backed up and restored as a unit.
For more information, see VSS Metadata.
You can use one or more of the following methods to identify if you are running into this issue:
Various backup applications may raise custom messages about
SqlServerWriter(or SQLWriter) not being found.
When you execute from a command prompt on the target SQL Server machine, you will not see
SqlServerWriterin the output.
vssadmin list writers