Data Protection Manager 2007 - SQL Server 2005 databases are not enumerated

Problem Description

When trying to protect SQL Server 2005 SP2 (patch version 9.00.3042.00) that is installed on a Windows Server 2008 machine with System Center Data Protection Manager 2007 you will not be able to enumerate the SQL databases for protection. Other data sources like volumes and system state will be enumerated but the “All SQL Servers” option is not available.

If you open a command prompt and run “vssadmin list writers” the SqlServerWriter is not listed.

You may notice the “SQL Server VSS Writer” service is running when viewing Services in Computer Management and if you try Stopping and Restarting the “SQL Server VSS Writer” service or rebooting the SQL Server the writer will still not be displayed
in "Vssadmin list writers". 

Further, when looking at the Application Event log on the SQL Server the following two events will be logged each time you try to enumerate the data sources from the protection group wizard in the DPM Administrator Console.

Log Name: Application
Source: VSS
Date: 9/3/2008 9:29:01 AM
Event ID: 8193
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: %SQLServerName%
Description:
Volume Shadow Copy Service error: Unexpected error calling routine
CoCreateInstance. hr = 0x80040154.

SECOND EVENT:

Log Name: Application
Source: VSS
Date: 9/3/2008 9:29:01 AM
Event ID: 32
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: %SQLServerName%
Description:
Volume Shadow Copy Service error: The VSS Coordinator class is not registered.
This may be caused due to a setup failure or as a result of an application's installer or uninstaller.

However – if you perform a SQL backup – the backup is successful.

Resolution

SQLWriter fails to create MetaData if any database name has leading or trailing spaces or other non printable characters. This causes SQLServerWriter to fail to display in VSSAdmin list writers.

Trailing space might be hard to spot just by looking at the Database names in SQL Server Management Studio. However, you can look at the Database properties and go to the “Files” tab and look for the Logical Name for the LOG FILE.

EXAMPLE: Note the space between the ‘TrailingSpace’ and the ‘ _log’ [TrailingSpace _log]

Procedure To View Database Names:

> Open SQL Server Management Studio

> Expand the SQL 2005 Databases

> Right click on each database and select Properties

> When the Database Properties screen appears select Files on the left column

> View the Logical Name for a leading or trailing space or non printable character

Author:
Thomas O'Malley
Senior Support Escalation Engineer
Microsoft Corporation

Technorati Tags: DPM,Data Protection Manager