Migrating data from WSS to SQL Server

I've been struggling with a Sharepoint related problem today. I've built up a Windows Sharepoint Services (WSS) server. By default, WSS uses SQL Server 2005 Embedded Edition to store its data. That's all good, however, when you do what I did you tend to hit a few snags. I installed WSS on a virtual server which is hosted on a physical server. Having used the WSS server for a while now, it has grown and is now the digital equivalent of a real world small elephant. However, the physical server has the digital capacity of a real world ladies hand bag. Problem ... little bits of elephant poking out everywhere. Not the most graceful of analogies, I know, but you get the gist.

So cutting a boring story short, I need to get the data off SQL Server 2005 Embedded Edition, and onto SQL Server NOT Embedded Edition somewhere else other than my virtual server. But for what ever reason, Microsoft have chosen to make it really hard for anything but Sharepoint to talk to SQL Server 2005 Embedded Edition. or SSEE. Imagine if you will, an INVISIBLE elephant in a ladies handbag. Weird.

SQL Server 2005 Embedded Edition goes by the name of MICROSOFT##SSEE. I installed SQL Server Management Studio Express to see if it would help but kept getting the following error:

Error: Cannot Connect to Server\MICROSOFT##SSEE.

An error occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections.

However, after much Googling, I discovered some genius has come up with a way to connect to this database. Simply use SQL Server Management Studio Express but call the database server name "\\.\pipe\mssql$microsoft##ssee\sql\query" when connecting. Work's like a charm.

Update: Some information on next steps from here can be found in this blog post on migrating WSS to SQL.