WSUS 3.0 : Connecting, Managing and Moving SUSDB as Internal Database
January 25, 2009 at 5:31 pm | Posted in WSUS | 41 CommentsTags: WSUS
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:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query 
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!
Cheers!
41 Comments »
RSS feed for comments on this post. TrackBack URI
Leave a Reply
-
-
About Me
Computer geek, totally fan of the latest's IT platform solutions. Since 2006 I've been mentioned as Microsoft Student Partner, I continue working with them, collaborating on different academic and technological events. On this blog, you'll find most of the experience I have evaluating, designing, implementing and managing those technologies.
-
-
App-V Books
Visitors Map
Recent Comments
Technorati
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
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!
Thanks again!!
Vince
Comment by Vince— April 22, 2009 #
Nice to hear you solved the problem :)
Cheers!
Augusto
Comment by Augusto Alvarez— April 22, 2009 #
Good post!!
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.”
Thanks!
Comment by Marcus— April 27, 2009 #
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 again!
Comment by Rich— June 2, 2009 #
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.
Comment by Ken— June 3, 2009 #
Thank you so much, I’ve been looking for directions on how to do this (unsuccessfully). I very much so appreciate your efforts!
David.
Comment by David— June 3, 2009 #
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.”
Comment by Anthony— August 31, 2009 #
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.
Comment by Anthony— August 31, 2009 #
Thanks Anthony!
Glad that helped you.
Cheers!
Augusto
Comment by Augusto Alvarez— August 31, 2009 #
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.
Bikini Browser
San Juan Puerto Rico
Comment by Bikini Browser— September 4, 2009 #
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.
Comment by Jeff Roberts— September 25, 2009 #
Sorry, missed the connect with: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Did the trick – thank you very much.
Comment by Jeff Roberts— September 25, 2009 #
Good to know Jeff :)
Cheers!
Augusto
Comment by Augusto Alvarez— September 25, 2009 #
Brilliant thanks very much !!!
Comment by Stirky— November 4, 2009 #
Will the download work on SBS 2003 r2?
Thanks ahead of time
Comment by fasteddie565— December 22, 2009 #
Which download are you refering to? The SQL Express will work in SBS 2003 R2.
Cheers!
Augusto
Comment by Augusto Alvarez— December 22, 2009 #
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
Comment by fasteddie565— December 22, 2009 #
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!
Cheers,
Augusto
Comment by Augusto Alvarez— December 22, 2009 #
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
Comment by fasteddie565— December 22, 2009 #
Good, let me know if I can help.
Cheers!
Augusto
Comment by Augusto Alvarez— December 22, 2009 #
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.
Comment by Chris Le— January 1, 2010 #
Great! Thanks for the feedback!
Cheers and happy new year! :)
Augusto
Comment by Augusto Alvarez— January 2, 2010 #
Augusto,
Thanks a lot for your document! I helped me a lot !
Comment by Juan Machado— January 26, 2010 #
Porуwnaj oferty i wybierz najlepsza.
Comment by Mariusz— March 10, 2010 #
Thanks – I’m familiar with SQL Server but not with WSUS – this answered my questions perfectly.
Comment by Nick Parker— March 30, 2010 #
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?
Comment by Daniel— April 15, 2010 #
Thank you much for this, was of great assistance and much appreciated.
Comment by Stephan Kramer— April 25, 2010 #
From http://msdn.microsoft.com/en-us/library/bb933794.aspx
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
1.
After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.
2.
Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.
3.
Right-click the database, and then click Properties.
The Database Properties dialog box opens.
4.
In the Select a page pane, click Options.
The current compatibility level is displayed in the Compatibility level list box.
5.
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).
Comment by Hansen— July 22, 2010 #
Hi
Thank you for the useful hint. Worked perfectly with just a downtime of a minute :)
Thanks!
Comment by Stefan— August 2, 2010 #
Worked like a charm. Thanks for the write up
Comment by Andy— October 13, 2010 #
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…
Comment by RAWNet— December 11, 2010 #
Quote:
“you can use the light and free version of Microsoft database ”
Caveat:
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″.
RØ
Comment by Rosalino— January 13, 2011 #
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).
RØ
Comment by Rosalino— January 18, 2011 #
Worked great for me… Although I too had to STOP www Publishing. Great information! Thank you very much!
Comment by Stan— March 1, 2011 #
[...] Parte 05 – Gerenciando a base de dados Categories: WSUS Tags: Augusto Alvarez LikeBe the first to like this post. Comments (0) Trackbacks (0) Leave a comment Trackback [...]
Pingback by WSUS 3.0 – Instalando e configurando « teocnologia— April 13, 2011 #
[...] An excellent howto can be found here: http://blog.augustoalvarez.com.ar/2009/01/25/wsus-30-connecting-managing-and-moving-susdb-as-interna… [...]
Pingback by Move WSUS data - Sentico IT Services— July 30, 2011 #
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)”
Comment by Shawn Bailey— December 19, 2011 #
Install WSUS 3.0 SP2 and run the procedure again.
Hope it helps,
Augusto
Comment by Augusto Alvarez— February 1, 2012 #
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.
Comment by Shawn Bailey— February 2, 2012 #
Thanks!
A clean way to move the wsus db and freeing diskspace on C:\ :-)
Comment by Marc Smets (@ShamFox)— October 26, 2012 #
[...] spiegazione dettagliata in lingua inglese la trovare qui Se volte invece spostare TUTTA la cartella WSUS, cosa consigliabile visto le dimensioni che prende [...]
Pingback by Spostare database WSUS (Windows Update Services) « Diario— April 25, 2013 #