SQL Server x Data Protection Manager 2012 – The VSS application writer or the VSS provider is in a bad state. (ID 30111) – English

I was using System Center Data Protection Manager (DPM) 2012 to make SQL Server backups.

Everything was going alright until one day I try to restore some backups in another SSQL Server instance, right from DPM and got this error message:

“The VSS application writer or the VSS provider is in a bad state. Either it was already in a bad state or it entered a bad state during the current operation. (ID 30111”

The message does not help much, since it points to some VSS writer error, which shows to be wrong after a short analysis.

After some research, where I noticed this error happens in a few different situations, I found out that in my case the problem was related to some databases with multiple (in my case there were two) backslashes (\\) on database file addresses, as this article mentions.

You can verify if this is the problem on your environment running these commands (on SQL Server GUI the multiple backslashes are hidden):

select physical_name from sys.master_Files

and/or

select filename from sysaltfiles

After this the bad news, there were no documented way to solve this, but since a few days before I had problems with SQL Server system tables, I started to wander a way to solve this.

Before explaining the solution I would like to make it crystal clear that this is a RISKY procedure and you should NOT do this if you are not aware of the impact it may cause!

So being short, DON’T DO THIS if you don’t know what’s really going on here and without making sure to test it previously!

The basic ideia is to change data on SQL Server system tables, correcting the multiple backslashes. The problem here is, by default, SQL Server (2005 and later) won’t let you update system tables.

What many people don’t know is that there is a way to make this (and it is good that most people don’t know this!).

Here is the procedure I have done:

1) STOP SQL Server

2) start SQL Server with these startup parameters:

a) -m – start SQL Server in single user mode

b) -c – not start SQL Server as a service

c) -T3608 – Trace flag that makes SQL Server to start only the MASTER database

This can be done using a GUI with SQL Server Configuration Manager (SSCM) or by command line

start sqlservr.exe -c -m -T3608 -s INSTANCE (s with tiny letters)

3) Connect to SQL Server, using SQLCMD, since when you use a GUI you will problably need more than one connection (you can make it with a GUI, but SQLCMD is easier)

sqlcmd -dmaster -A -U USER -P PASSWORD -S SERVER\INSTANCE (S with CAPITAL letters)

Its worth mentioning that we are using the -A parameters, what means that we are using a  DAC connection, what needs that this resource has been activated using sp_configure.

4) After connected, execute these commands:

USE MASTER
GO

select physical_name from sys.master_Files where database_id > 4 (preferi evitar os bancos de sistema, só por garantia)

update sys.master_files set physical_name = replace(physical_name,’\\’,’\’) where database_id > 4

select physical_name from sys.master_Files where database_id > 4

select filename from sysaltfiles (só por garantia, para conferir)

The interesting point is that you don’t even need to activate the option “allow updates” on the instance!

/*
sp_configure ‘allow updates’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
*/

With this procedure the extra backslashes were removed and the restores started to work with no problems!

Esse post foi publicado em Artigos, System Center. Bookmark o link permanente.

Uma resposta para SQL Server x Data Protection Manager 2012 – The VSS application writer or the VSS provider is in a bad state. (ID 30111) – English

  1. Pingback: SQL Server x Data Protection Manager 2012 – The VSS application writer or the VSS provider is in a bad state. (ID 30111) – Situation 2 | Vladimir M. B. Magalhães – Learn and Share

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s