Symptoms
When you use Volume Shadow Copy Services (VSS) based applications to backup 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 backup a volume that contains one of these databases.
Cause
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 backup these databases or any volumes that contains these databases.
Resolution
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:
Query: SELECT database_id as DatabaseID, '##'+name+'##' as DatabaseName from sys.databases
Example output:
- DatabaseID DatabaseName
- 8 ##AdventureWorks## -- DB name is fine
- 15 ## DBWithLeadingSpace## -- DB name contains leading spaces
- 17 ##DBWithTrailingSpace ## -- DB name contains trailing spaces
Note: In the above query, if the database name contains unprintable characters they may either be printed out as spaces or some junk.
More Information
Writers (like SQL Writer) add components using IVssCreateWriterMetadata::AddComponent, specifying the following component information:
- Type
- Name
- Logical path (if any)
- Supported feature
- Selectability
- Metadata to be used by the writer during restore
- Display information
- Notification information
Components 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 additional information see http://msdn.microsoft.com/en-us/library/aa384953(VS.85).aspx
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 'SqlServerWriter' in the output.
vssadmin list writers