Use Unitrends to back up SQL Server Express 2012 while WSUS installed on same server

Introduction When errors occur with VSS writers during a SQL backup, the problem may not be with the database you want to backup, but ...

Introduction

When errors occur with VSS writers during a SQL backup, the problem may not be with the database you want to backup, but another database on the same server.

In my case, the WSUS database uses similar SQL Server 2012 technology but prevented my backup from succeeding. These steps allow you to backup a SQL Server Express 2012 instance successfully.

Steps (6 total)

1

SQL Backups keep failing

Expand

Using the Unitrends Recovery 714s, I made file-level backups successfully from a Windows Server 2012 R2 Standard virtual machine. Previously, I used the Unitrends appliance connected to other servers to backup databases running MS SQL Express version 2005 and version 2008.

Now, we added an application that uses MS SQL Server 2012 Express. When I run the database backup, I kept getting several error messages from the Unitrends appliance (see image) and the server's Application Log (see below).

***
Log Name: Application
Source: Unitrends Agent
Date: 8/4/2015 11:47:09 AM
Event ID: 277
Task Category: Backup
Level: Error
Keywords: Classic
User: N/A
Computer: xxx.yyy.zzz.com
Description:
(id 8868) Inline verify mismatch between agent and appliance.

2

Check credentials used for backing up

Expand

I started to examine the accounts used to ensure they have sufficient credentials to backup. I already knew that the Active Directory account "unitrends" worked to backup other servers & database. Perhaps there was another location I needed to set permissions.......

I checked the documentation to see if I missed something
(SQL Server protection from the Unitrends documentation....)
http://www.unitrends.com/documents/administrators-guide/index.htm#user_manual/sql/about_sql_server_protection.htm

My first Google search for my error yielded this Spiceworks thread:
Unitrends SQL Backups Fail
http://community.spiceworks.com/topic/412202-unitrends-sql-backups-fail

The documentation and discussion pointed me to use the SQL Server Management Studio (free download from Microsoft) to check the SQL "Server Roles" that the backup account was using.
I added the "unitrends" account to the SQL Express database instance under "Logins".
Then I looked at the "Login Properties" and "Server Roles" section.
I checked the "sysadmin" role, saved my changes and tried the backup again....

No luck.... my backups kept failing

3

Identify the initial error that causes the backups to fail

In the Unitrends log, I saw a few messages that were related.

- Message: " FATAL: Incomplete backup received from client. "
- My response: FATAL is always a bad sign. I don't like being in a FATAL state. My career would take an extreme downturn.
- What is really happening: The backup was not complete

- Message: "Server Archive Checksum...: ERROR: Client archive checksum does not match Server archive checksum. Verification failed."
- Message: "Exit Status....: Verify was bad"
- My response: Maybe the backup did finish but the Unitrends appliance couldn't confirm it was good
- What is really happening: The verification phase had problems

- Message: "Unable to perform backup operation, detecting that 'WIDWriter' writer is in failed state!
- Status: 12 'VSS_WS_FAILED_AT_BACKUP_COMPLETE'
- Writer Failure code: 0x00000000 (S_OK)
- Writer ID:'{8d5194e1-e455-434a-b2e5-51296cce67df}'
- Instance ID:'{04ab53e0-17c2-4b19-bdb8-00c1bee26a95}'
Unitrends agent failed complete backup, terminating application backup."
- My response: Who is "WIDWRITER" and why did it fail?
- What is really happening: a Volume Shadow Copy Service writer did not respond well during the backup

Now I am getting somewhere...

4

What is the "WIDWriter"?

The Volume Shadow Copy Service creates a snapshot of files, services or databases for backup & recovery purposes. The "writers" are components that perform these actions and behave independently of each other. I have several installed on my server so I have to examined the specific VSS Writer "WIDWriter" status to figure out what is going on.

Open an Administrator Command prompt and run "vssadmin list writers". This will tell us the State and Errors for all VSS writers on the server.

This is the result I got..
***
Writer name: 'WIDWriter'
Writer Id: {8d5194e1-e455-434a-b2e5-51296cce67df}
Writer Instance Id: {04ab53e0-17c2-4b19-bdb8-00c1bee26a95}
State: [5] Waiting for completion
Last error: No error
***

WIDWriter corresponds to the "Windows Internal Database VSS Writer". The Windows Server Update Services (WSUS) uses a specialized version of SQL Server 2012 including a modified database VSS writer.

Great, now I know where the problem lies, what can I do about it?

More info about VSS Writers
http://www.planetcobalt.net/sdb/vss_writers.shtml

5

What does Microsoft have to say about VSS Writers

Windows Server Backup May Fail Because of the SQL VSS Writer
https://support.microsoft.com/en-us/kb/2615182

I found this MS knowledge base article detailing steps to find the database that is going awry. While it talks about Windows SBS server, the principle applies to this situation.

In my case, the WSUS internal database was interfering with successful backups thru a misbehaving VSS writer, "WIDWriter". That bad VSS writer was making ALL of the other VSS writers fail on that volume.

When I stopped all the WSUS services, I reran my backup.

SUCCESS!

6

How to schedule the backups to finish successfully

Expand

I need to stop all the WSUS services BEFORE the Unitrends SQL backup job runs. Initially, I thought there were options in the Unitrends appliance to accomplish this.

It turns out the "Pre-Backup Commands" and "Post-Backup Commands" will work only if you are backing up a complete CLIENT. If I was doing a complete server backup, I could use that feature.

I am backing up an APPLICATION (SQL Server, specifically), so I have to deal with stopping and starting the WSUS services some other way.

My Unitrends SQL backup is scheduled to run at 2:00 am every day. Therefore, on the server, I created 2 items in the Task Scheduler.
Task 1 runs at 1:50 am to STOP all the WSUS services.
Task 2 runs at 2:10 am to START all the WSUS services.

My SQL database is not that big and backups complete in 1-2 minutes. Therefore, the 10 minutes before and after 2:00 am is plenty of time to finish.

Here are the 2 windows .CMD files I use to start and stop the WSUS services:

------
stop-wsus-services.cmd
***
net stop "wsus service"
net stop "windows internal database"
net stop "windows internal database vss writer"

------
start-wsus-services.cmd
***
net start "windows internal database"
net start "windows internal database vss writer"
net start "wsus service"

Conclusion

I wasn't sure where to file this as it applies to Unitrends appliances, MS SQL server and WSUS. You could adapt the steps for a different backup method or different timings.

Leave any comments and I'll update this How-To

Good Luck!

Michael Z.

References

• Connecting to databases using SQL Server Management Studio
• Unitrends Documentation - About Backup Options
• Windows Server Backup May Fail Because of the SQL VSS Writer
• More info about VSS Writers
• Unitrends SQL Backups Fail
• SQL Server protection from the Unitrends documentation