Troubleshooting SQL Server Protection Issues

Article
05/17/2011

Applies To: System Center Data Protection Manager 2007

The following table provides troubleshooting guidance on protection issues for computers running SQL Server.

SQL Server Protection Issues

Issue	Possible Cause	Resolution
DPM is unable to protect a SQL Server database.	You must have the required software installed to protect a computer running SQL Server.	The following versions of SQL Server must be installed on the instance of SQL Server that is hosting the database that you want to protect: Microsoft SQL Server 2000 with Service Pack 4 (SP4) - OR - Microsoft SQL Server 2005 with Service Pack 2 (SP2) Note DPM supports Standard, Enterprise, Workgroup, and Express Editions of SQL Server. Important You must start the SQL Server VSS Writer Service on the SQL Server. By default, the SQL Server VSS Writer Service is turned off when you install SQL Server 2005. To start the SQL Server VSS Writer Service Click Start, point to Administrative Tools, and then click Services. On the Services screen, scroll down and right-click SQL Server VSS writer, and then click Start. Warning After you start the SQL Server VSS Writer Service, we recommend that you do not stop the service. If your backups begin to fail, check to ensure that the SQL Server VSS Writer Service is started.
The SQL Server VSS Writer service fails during protection.	If you are protecting SQL Server 2005 on a Windows Server 2008 operating system, you must install SQL Server 2005 SP2.	For information about running editions of SQL Server on Windows Server 2008, see Running SQL Server on Windows Server 2008 or Windows Vista (https://go.microsoft.com/fwlink/?LinkId= 130555).
The SQL Server database backup fails.	If you physically delete one of the database files from the SQL Server and do not remove it from the database, the SQL Server VSS Writer Service will continue to report the deleted file and DPM will not be able to locate it while performing backups.	Delete the database log files using the following query: ALTER DATABASE <file name>.mdf REMOVE FILE <file name>_log.ldf Note that you will not be able to restore to the original location from the backups that existed before you deleted the log files because the file does not exist. To resolve this issue, you must restore the database files, and then attach the database to the instance of SQL Server.
When you perform an inquiry job in the DPM New Protection Group Wizard, such as viewing a list of data sources, if two different SQL Server VSS Writers are enumerating the same data source, the DPM server will crash.	This can occur in the following scenario, where you have two SQL Server VSS writers: SqlServerWriter and MSDEWriter. You install an instance of SQL Server 2005. (The SQL Server VSS Writer Service is turned off by default) Because the SQL Server VSS Writer Service is turned off, the MSDEWriter writer enumerates the SQL Server 2005 databases. You turn on the SQL Server VSS Writer Service in the Service Control Manager, which sets the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VSS\Settings\MSDEVersionChecking registry key value to 1. After the registry key value is set to 1, the MSDEWriter writer stops enumerating the SQL Server 2005 databases, and the SQLServerWriter writer starts enumerating them. You then set the value of the registry key to zero (0), which causes both the MSDEwriter and SQLServerWriter writers to start enumerating the SQL Server 2005 databases.	To resolve this issue, you must set the value of the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\VSS\Settings\MSDEVersionChecking registry key to 1 (a non-zero value), so the MSDEWriter is the only writer that enumerates the SQL Server 2005 databases.
Database protection on an instance of SQL Server 2005 fails.	If you are protecting a database on an instance of SQL Server 2000 and then upgrade to SQL Server 2005, database protection on the instance of SQL Server 2005 will fail.	After you upgrade to SQL Server 2005 and then restart the SQL Server VSS Writer service, DPM will not map the existing replica to the protected database. This is because the new VSS writer enumerates the new database, and therefore DPM recognizes it as a new data source. If this issue occurs, we recommend that you stop protection for the database that is hosted on the upgraded instance of SQL Server 2005, and then use the Create New Protection Wizard to reconfigure protection. Note After you reconfigure protection, DPM Administrator Console displays the protected database as two separate nodes. The protection status in the Protection task area appears as Inactive replica for one of the database nodes, and the DPM Recovery user interface displays two database nodes with the same name. No data loss occurs with this issue.
SQL Server 2000 database replica creation job fails running on an x64 bit computer.	Replica creation jobs may fail on a SQL Server 2000 database running on an x64-bit computer in Windows in Windows (WOW) mode.	If a replica creation job fails on a SQL Server 2000 database running on an x64-bit computer in Windows (WOW) mode, check the event viewer on the SQL Server computer for Event ID 20 "Volume Shadow Copy Service error." For information about a supported hotfix to correct this issue, see Event ID 20 (https://go.microsoft.com/fwlink/?LinkId=73718).
SQL Server full transaction log causes synchronization job failures.	If the database transaction log unexpectedly becomes full on a computer that is running SQL Server, your synchronization jobs will start to fail.	For information about how to resolve this problem, see Microsoft Knowledge Base article 317375, "A transaction log grows unexpectedly or becomes full on computer that is running SQL Server "(https://go.microsoft.com/fwlink/?LinkId=72730).
DPM protection jobs start to fail.	If DPM is protecting a SQL Server database that is using mirroring or log shipping, after a failover the protected primary database will no longer be online and your DPM protection jobs will start to fail.	If you plan to fail-back to the primary database, do the following: After your protection jobs start to fail and the replica becomes invalid, perform a consistency check. Note If you fail-back to the primary database in a short period of time and the replica remains valid, then you do not have to perform a consistency check. If there is a delay in the fail-back to the primary database, you must create a protection group using the New Protection Group Wizard to protect the new active database. Note After the failover, you can stop protection on the new protection group. If you do not plan to fail-back to the primary database, we recommend that you remove it from protection and then configure protection for the new database. To remove the primary database, do the following: In DPM Administrator Console, click Protection on the navigation bar. In the Protection task area, select the database that you want to remove, and then in the Actions pane, select Remove from protection. In the Stop Protection - <Protection Group name> dialog box, select the Retain Protected Data option, and then click Stop Protection. After you remove the primary database, configure protection for the new database using the New Protection Group Wizard.
Incremental synchronizations fail for the SQL Server master database.	DPM does not support configuring incremental synchronizations for SQL Server master databases.	DPM allows log backups for databases configured in the full and bulk-logged recovery models. However, log backups cannot run against the SQL Server master database. Additionally, do not set the synchronization frequency for the master database to "just before a recovery point," because there are no backup logs to obtain from the protected server during the recovery.
DPM is not protecting SQL Server 2005 databases.	SQL Server 2005 includes database snapshots that appear in the New Protection Group Wizard as regular databases. However, DPM does not support protecting database snapshots for recovery.	You can view database snapshots in the Microsoft SQL Server Management Studio in the Database Snapshots folder.
Replica point creation fails with “Unexpected error. 0x800700A1 (Path specified is invalid).	This can happen in a mirrored environment if the database is on differently named volumes on each mirror, and a failover occurs.	Stop protection with retain tape data. Then start protection again.