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

44 Comments

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!

Categories: WSUS

Tagged as:

44 Comments »

  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

    Reply

  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!

    Reply

    • 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

      Reply

  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!

    Reply

  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.

    Reply

  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.

    Reply

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

    Reply

  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.

    Reply

  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

    Reply

  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.

    Reply

  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

    Reply

  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.

    Reply

  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?

    Reply

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

    Reply

  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…

    Reply

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

    RØ

    Reply

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

      Reply

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

    Reply
  28. Pingback: WSUS 3.0 – Instalando e configurando « teocnologia
  29. Pingback: Move WSUS data - Sentico IT Services

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

    Reply

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

  32. Pingback: Spostare database WSUS (Windows Update Services) « Diario
  33. Pingback: Spostare database WSUS (Windows Update Services) | Joe's Personal Blog

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

    Reply

Leave a comment