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
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)
4) After connected, execute these commands:
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
RECONFIGURE WITH OVERRIDE
With this procedure the extra backslashes were removed and the restores started to work with no problems!