Freigeben über

Backup Software fails to take System State backup if SQL server VSS writer Service is running

  • Artikel

This blog describes a very simple but tricky issue. A Backup software was unable to take system state back up. The issue was narrowed down to a situation where the SQL Server VSS writer service fails to connect to a non existing SQL instance. When we would stop “SQL Server VSS writer service”, everything except for SQL backup would work fine.

Here are some steps which I followed to troubleshoot the issue:

1. We ran “vssadmin list writers” to enumerate the writers from the command prompt and saw messages similar to:

Vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool

(C) Copyright 2001-2005 Microsoft Corp.

Waiting for responses.

These may be delayed if a shadow copy is being prepared.

2. After disabling SQL server VSSwriter service the results were shown within a second.

3. Checked application logs and found the following error:

Error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80004005. SQLSTATE: HYT00, Native Error: 0 Source: Microsoft SQL Native Client Error message: Login timeout expired SQLSTATE: 08001, Native Error: 2 Source: Microsoft SQL Native Client Error message: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. SQLSTATE: 08001, Native Error: 2 Error state: 1, Severity: 16 Source: Microsoft SQL Native Client Error message: Named Pipes Provider: Could not open a connection to SQL Server [2].

DBPROP_INIT_DATASOURCE: Server Name à Default instance name.

DBPROP_INIT_CATALOG: master

DBPROP_AUTH_INTEGRATED: SSPI

4. The following article SQL Server 2005 connectivity and Volume Shadow Copy Service (VSS) discusses a lot of errors like the one seen above

5. From the above error we can clearly see that there’s a failure trying to connect to the default instance

DBPROP_INIT_DATASOURCE is set to Computer name for default Instance and , [computer name\instance name] for a named Instance.

6. Troubleshooting in this scenario should essentially begin with checking connectivity to the default instance.

7. Checked Service Control manager for a default instance and didn’t find it there. The only other place DBPROP_INIT_DATASOURCE was getting the instance names, would be enumerated under the following hive

a. ”HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL”

8. Checked the registry entry ”HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL” and found an entry for the default instance {Machine Name}while the service did not exist on the machine.

9. Took the backup of the registry and removed the <MACHINE NAME> {for the default instance} entry from the above hive.

10. Running the command “vssadmin list writers” instantly generated an output this time

11. Tried the backup again and everything worked perfectly.

Harsh Chawla,

SE, SQL support

Reviewed BY:

Nickson Dicson & Shamik Ghosh

TL, SQL support

Amit Banerjee

SEE, SQL support

Comments

  • Anonymous
    March 30, 2014
    Thank you for the solution, it worked for me.