WSUS 3.0 : Connecting, Managing and Moving SUSDB as Internal Database
Like you should know, WSUS gives you the choice, at the moment of installation, to choose a database instance where you would like to keep the WSUS database (SUSDB): Internal database (formerly known as SQL Server embedded), an existing database or an existing database on a remote computer.
Sometimes, for example if you are just spiking about WSUS and you don’t want to install any version of SQL Server, you best choice would be the Internal Database option. But what happens if you change your mind later and you want to change the location of that internal database to a different drive? Or simply executing other tasks on the database, like generating a backup or shrinking?
Fortunately there’s a way to connect to that DB without having to re install WSUS:
Using SQL Server Management Studio
Note: If you don’t have it, you can download the stand alone tool for SQL 2005 version: Microsoft SQL Server Management Studio Express; or you can download SQL Server 2008 Express Edition (the latest of the free SQL Server versions, includes among the tools the SQL Server 2008 Management Studio Express).
1. Open SQL Server Management Studio. In my case, I’m using SQL Server 2008 Express Edition.
2. Using the connection window, connect to this instance:
3. You should see now in Object Explorer the database instance connection. Right-click on SUSDB –> Tasks –> Detach
4. Check the options for “Drop Connections” to remove all active connections to that DB. Click OK.
5. A message saying that the DB is not accessible should appear. Click OK.
6. Move the database files to the new drive or location where you are planning to keep it.
7. Back to the instance connection. Right-click Databases –> Attach.
8. Click on “Add” and select the .mdf folder where you relocated the database.
9. Click OK to attach it again.
10. Your database should be working now on the new location.
Using SQL functions / SQLCMD
Note: If you want to use SQLCMD command line utility you can download it from the Microsoft SQL Server 2008 Feature Pack, where you’ll find plenty of SQL utilities.
1. Run “cmd” and locate it where the sqlcmd executable is. Usually: %Program Files%\Microsoft SQL Server\90\Tools\binn.
2. Type in “sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query –E” press Enter.
3. You are now connected to Internal Database instance. Type “EXEC sp_detach_db @dbname = ‘SUSDB’”.
4. On the next query line, type “GO” and press Enter.
5. Exit the SQLCMD utility. Type “EXIT” and then type “GO”.
6. Move the database files to the new file location.
7. Reconnect to the Internal Database instance: “sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query –E”.
8. Attach it again: “EXEC sp_attach_db @dbname = ‘SUSDB’, @filename1 = ‘<path>SUSDB.mdf’, @filename2 = ‘<path>SUSDB_log.ldf’”.
9. Exit the SQLCMD utility. Type “EXIT” and then type “GO”.
Even though you now know how to access the internal WSUS database, I would recommend to migrate this internal database to a SQL Server database if you have the chance, and remember that you can use the light and free version of Microsoft database engine: SQL Server Express Edition (2005 or 2008 version).
To execute the migration check this link:
Migrating from Windows Internal Database to SQL Server
Hope you find it useful!
Thanks for putting this out there! I’m not a DB admin in any sense. These instructions helped me re-attach the WSUS DB after failing to re-attach with the SQLCMD command. The MS Management Studio worked real well in this case!
Nice to hear you solved the problem :)
I am have problems with SQLCMD
“Msg 3703, Level 16, State 2, Server VM-SERVER01\MICROSOFT##SSEE, Line 1
Cannot detach the database ‘SUSDB’ because it is currently in use.”
I found Process w3wp.exe keeping from removing Database. When you Kill the process it restarts, but I tricked it and removed database before process could restart. Worked for me
I just wanted to thank you for the post, it helped me out. I don’t know why Microsoft can’t just post something as straight forward as this to begin with.
Thanks! Great post. This worked like a charm for me as well and I have been struggling using other methods for some time! Much appreciated.
Thank you so much, I’ve been looking for directions on how to do this (unsuccessfully). I very much so appreciate your efforts!
Brilliant! Thank you – I’ve been struggling with this problem and all other sites assume SQL Knowledge. As a complete novice, I found your instructions, concise and easy to follow. I totally agree with Rich in an earlier post, “…I don’t know why Microsoft can’t just post something as straight forward as this to begin with.”
Brilliant! Thank you – I’ve been struggling with this problem and all other sites assume SQL Knowledge. As a complete novice, I found your instructions, concise and easy to follow.
Glad that helped you.
I looked everywhere for a simple document on how to do this, but I couldn’t find one anywhere. Your document solved 100% of my problems.
Thanks for the post.
San Juan Puerto Rico
This is good information. Unfortunately I get a “Cannot connect to server\MICROSOFT##SSEE. When I connect to server\WSUS all I see is the system databases.
Sorry, missed the connect with: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Did the trick – thank you very much.
Good to know Jeff :)
Brilliant thanks very much !!!
Will the download work on SBS 2003 r2?
Thanks ahead of time
Which download are you refering to? The SQL Express will work in SBS 2003 R2.
The SQL express edition will not install due to the web installer not being compatible with SBS 2003. Is there anyone that looks at these issues, this is insane just to have to move a file
Good to know that, thanks for the input. Have you check the guide to migrate de database from Internal to SQL? http://technet.microsoft.com/en-us/library/cc708558(WS.10).aspx
In that case, using SQL Management Studio you will be able to move it.
Hope it helps!
Actually if you go to the link at the bottom of the page you can download the file, but now I am receiving an error in writing to temp files when trying to install.
I am installing a new server soon and do not want to spend anymore time on this than necessary. I will keep trying
I will keep you posted, thanks
Good, let me know if I can help.
WORKED with SBS 2003 R2!
First I had to stop World Wide Web Publishing Services first. Then I had to stop Update Services. Then the instructions worked for me. Thanks.
Great! Thanks for the feedback!
Cheers and happy new year! :)
Thanks a lot for your document! I helped me a lot !
Porуwnaj oferty i wybierz najlepsza.
Thanks – I’m familiar with SQL Server but not with WSUS – this answered my questions perfectly.
This is exactly what Im after, I have stopped w3svc and update services, detached the database, but when I go to move the mdf files, I cant move them as they are in use by another person or program. Am I missing something?
Thank you much for this, was of great assistance and much appreciated.
For your information, changing the compatibility seems to solve high memory issue after migration!
*I’ve tested on SBS 2003! Does not break any tools! Cool!
Try this steps:-
To view or change the compatibility level of a database
After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
Right-click the database, and then click Properties.
The Database Properties dialog box opens.
In the Select a page pane, click Options.
The current compatibility level is displayed in the Compatibility level list box.
To change the compatibility level, select a different option from the list. The choices are SQL Server 2000 (80), SQL Server 2005 (90), or SQL Server 2008 (100).
Thank you for the useful hint. Worked perfectly with just a downtime of a minute :)
Worked like a charm. Thanks for the write up
if anyone has trouble after OK’ing the ‘drop connections’ checkbox at step 4, open a command prompt and run ‘net stop “update services”‘ and try again. restart the update services using ‘net start “update services” after you have completed the move…
“you can use the light and free version of Microsoft database ”
SQL Express has a database size limit of 4GB.
When in SBS2008, you must config WSUS for retrieving almost “everything” and you easyly reach that 4GB limit.
I migrated according to your Technet link (but using your GUI method, thanks!).
Now, I have reached those 4GB: perhaps I didn’t clean SUSDB enough frecuently :-(
I’ve reverted to internal database using the same process (inverse, of course) and setting HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\SqlServerName to “computername%\MICROSOFT##SSEE” and setting HKLM\Software\Microsoft\Update Services\Server\Setup\wYukonInstalled to “1”.
There must be something I didn’t reverted correctly: clients may update but they cannot report status: the Reporting Web Service fails to connect to SQL.
Besides, I someway broke WSS (doesn’t matter: presently it is of no use for me).
Worked great for me… Although I too had to STOP www Publishing. Great information! Thank you very much!
I’m having an issue re-attaching due with they following error message. Any ideas here?! This seems to be exactly what I need to get this BS working again…
“The database ‘SUSDB’ cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported.
Could not open new database ‘SUSDB’. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)”
Install WSUS 3.0 SP2 and run the procedure again.
Hope it helps,
You know, we had to get on with MS support and they did everything. There were a ton of hidden settings they had to work through and the installers we’d downloaded didn’t do anything. It was a nightmare and a complete joke. I despise MS.
A clean way to move the wsus db and freeing diskspace on C:\ :-)
Hi i go to migrate the WSUS Server 3.2 to 6 (Windows server 2008 r2 to Windows server 2012 r2 ) , we are maintaining the WSUS database local system only C\Wsus , how to take backup this data and restore the same database in windows server 2012 r2