WSUS 3.0 : Connecting, Managing and Moving SUSDB as Internal Database

January 25, 2009 at 5:31 pm | Posted in WSUS | 42 Comments
Tags:

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!

About these ads

42 Comments »

RSS feed for comments on this post. TrackBack URI

  1. 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

  2. Nice to hear you solved the problem :)
    Cheers!
    Augusto

  3. 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!

  4. 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!

  5. 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.

  6. Thank you so much, I’ve been looking for directions on how to do this (unsuccessfully). I very much so appreciate your efforts!

    David.

  7. 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.”

  8. 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.

    • Thanks Anthony!
      Glad that helped you.
      Cheers!
      Augusto

  9. 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

  10. 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.

  11. Sorry, missed the connect with: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

    Did the trick – thank you very much.

  12. Good to know Jeff :)
    Cheers!
    Augusto

  13. Brilliant thanks very much !!!

  14. 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.
      Cheers!
      Augusto

  15. 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!
      Cheers,
      Augusto

      • 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.
        Cheers!
        Augusto

  16. 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! :)
      Augusto

  17. Augusto,

    Thanks a lot for your document! I helped me a lot !

  18. Porуwnaj oferty i wybierz najlepsza.

  19. Thanks – I’m familiar with SQL Server but not with WSUS – this answered my questions perfectly.

  20. 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?

  21. Thank you much for this, was of great assistance and much appreciated.

  22. 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).

  23. Hi

    Thank you for the useful hint. Worked perfectly with just a downtime of a minute :)

    Thanks!

  24. Worked like a charm. Thanks for the write up

  25. 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…

  26. 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”.

    • 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).

  27. Worked great for me… Although I too had to STOP www Publishing. Great information! Thank you very much!

  28. […] 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 […]

  29. 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,
      Augusto

      • 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.

  30. Thanks!

    A clean way to move the wsus db and freeing diskspace on C:\ :-)

  31. […] spiegazione dettagliata in lingua inglese la trovare qui Se volte invece spostare TUTTA la cartella WSUS, cosa consigliabile visto le dimensioni che prende […]

  32. […] spiegazione dettagliata in lingua inglese la trovare qui Se volte invece spostare TUTTA la cartella WSUS, cosa consigliabile visto le dimensioni che prende […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.

Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: