Access and backup the WSUS 3.0 database

The Windows Update Services 3.0 store all their metadata in a SQL-database. It doesn't come with a built-in way to back it up though. Rather than using an existing SQL-Server installation or SQL Server Express it is using the 'Windows Internal Database (WID)' a special SQL-Server instance that is also used by other Microsoft products such as Sharepoint. WID doesn’t come with any tools to access it and it doesn’t allow remote access from existing SQL Server tools. I didn’t want to install any SQL tools on the server and also didn't want to hack the registry to change these settings but I wanted to backup the database and also have a look at it.

I first copied sqlcmd.exe and its components to the server and try to run it, but it is using the 'SQL Native Client' which is not installed.

I then copied QueryExpress.exe which is a single file non-install 'Query Analyzer' clone (available for free at http://www.albahari.com/queryexpress.html) In the server field of the 'Connect…' dialog I typed:

\\.\pipe\mssql$microsoft##ssee\sql\query

\\.\pipe\Microsoft##WID\tsql\query

This connects to the local server using named pipes, microsoft##ssee is the instance name of the Windows Internal Database.

It connected fine and I can now have a look at the database structure of the SUS database. To back it up use the following TSQL:

BACKUP DATABASE SUSDB TO DISK = N'C:\Backup\SQL\susdb.bak' WITH NOFORMAT, NOINIT, NAME = N'SUSDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 

This is cool, but now I wanted to automate the backup. I wrote a Powershell script that connects to the server and executes the same TSQL command as above. Unfortunately I couldn't get it to connect to the server, I tried various different connection strings and finally gave up.

I knew that QueryExpress.exe is using ADO.Net to connect, so I wrote a small .net console app to back up the database and any databases for that matter. Just specify the Name of the backup file in the config file and execute the program on the same server WSUS is installed.

You may want to delete the previous backup file before running it because these files are over a gig in size, at least on my server. If you don’t delete it, it will append the data to the existing file.

Download the backup tool from my downloads page.