I needed to move my WSUS database from SQL 2005 to a new SQL 2008 R2 server. It was difficult to find the exact directions online so I hashed together some from several places and it worked just fine. Here's how.
1. On the WSUS server, stop the "IIS Admin" service and "Update Services" service from services.msc.
2. On the SQL 2005 server, backup the SUSDB.
Right click on the running SUSDB database in SQL Management Studio and select All Tasks -> Backup.
Database = SUSDB
Backup Type = Full
Destination = Disk. I chose the desktop.
Options Page = I selected to Verify the database when finished. Press OK.
3. Copy the database over to the new SQL 2008 R2 server.
4. On the new SQL 2008 R2 server, open SQL Management Studio and connect to the database engine. Then click on New Query.
5. Type in the following and then execute by pressing the green checkmark in the toolbar:
USE MASTER
GO
ALTER DATABASE SUSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SUSDB
GO
6. Once that is done right click on Databases and select Restore Database.
To database = SUSDB
From database = From device and then find the file you copied over from the old SQL server.
Under Select the backup sets to restore = put a check mark next to the SUSDB database to restore.
Press OK.
7. Back on the WSUS server, open regedit.exe and navigate to HKLM\Software\Microsoft\UpdateServices\Server. Find the SqlServerName object and change that to the name of your new SQL server.
8. Restart the "IIS Admin" and "Update Services" services.
9. Open WSUS admin console and give it a moment to connect.
That's it. It worked perfectly for me and was really easy to do.
