Backing up a SQL Server database using a VSS backup application may fail for some databases


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:

  1. Writer identification and classification information,
  2. Writer-level specifications
  3. 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