Managing the Windows Internal Database (SQL Server 2005 Embedded Edition)

As a follow up to the entry on Windows Internal Database, I keep getting questions on how to manage this if in case you do not want to remove it. The first time I heard about this, I thought Microsoft may have missed out on informing me about the SQL Server 2005 Embedded Edition as this does not appear in the list of Editions for SQL Server 2005. This is the replacement for WMSDE which a few of the Microsoft products - Windows Sharepoint Services 3.0 and Windows Software Update Services 3.0, for example- use as a backend database. Most of the questions I get are administration related like how to shrink the database files, move them on a different partition, etc. By default, there is no tool available to manage the database hosted in this instance (MICROSOFT#SSEE). But you can use the available SQL Server 2005 tools to do administration and maintenance. The simplest there is will be to install SQL Server 2005 Management Studio Express. This is an available download from the Microsoft Download Center. You just have to register this instance on the Management Studio Express to be able to administer it. One thing to remember is that during the registration process, you have to specify the Network Protocol option in the New Server Registration window to be Named Pipes as this is the one being used by this instance. Once connected, you can now start managing the databases running on this instance. Another way is to use the sqlcmd tool. This is the command-line utility of choice for SQL Server 2005, although you can still use the osql utility. You need to download the SQL Server 2005 Native Client and SQL Server 2005 Command Line Query Utility from the Microsoft Download Center. You need to install the Native Client first before installing the Command Line Query Utility. Once installed, you can run sqlcmd.exe from the command line. This is typically installed in this directory

C:\Program Files\Microsoft SQL Server\90\Tools\binn

To connect to the Windows Internal Database instance (MICROSOFT##SSEE), run this command

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE \sql\query –E

Once connected, you can now execute those TSQL scripts that you are familiar with to manage and administer databases in this instance.

NOTE: This post was originally from my old blog site