First off, the environment I'm operating under runs WSUS 3.0 SP2 on a Windows Server 2008 R2 64-bit box that is fully patched. WSUS utilizes the Windows Internal Database, and not a full blown SQL database.
The first thing you'll need is "sqlcmd". I tried just copying sqlcmd.exe from one of my SQL servers, but that didn't turn out so well. I was worried that I was going to have to install all of SSMS on my WSUS server, which wouldn't be horrible, but I like to keep things tidy and this seemed like overkill. Some research led me to this download page for the SQL 2005 Feature Pack.
- Download the Microsoft SQL Server Native Client from that download page (skip the red "Download" button at the top for ala carte offerings below; more rejoicing).
- Scroll down farther and also grab the appropriate Microsoft SQL Server 2005 Command Line Query Utility.
- On your WSUS server, install the Native Client, and then install the Command Line Query Utility. In my case, sqlcmd.exe was created in C:\Program Files\Microsoft SQL Server\90\Tools\binn.
- Now, copy the WSUSDBMaintenance.sql script on Technet to a folder (I'll use C:\PS).
- Create a new batch file in C:\PS containing the following:
cd "C:\Program Files\Microsoft SQL Server\90\Tools\binn"
sqlcmd -I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i C:\PS\WsusDBMaintenance.sql
- Finally, schedule the task on the WSUS server so it runs once a month. I picked the last Sunday.
If you're familiar with the blog, you may wonder why I'm not using a Powershell script. :)
The answer is because this is the easiest way to implement this tiny operation. Why fool around with Invoke-Expression when a batch file works just fine?
When I ran this for the first time, I assumed it would take a long time, since I'd never done it before. To my surprise, the process took about 3 minutes.