I have a couple of .NET 2.0 winfor applications that I use Click once to deploy them, these application use Sql Express to store data locally. I needed to import data using managment studio, but User Instance databases do not appear in Management Studio. After some research I was finally able to do this.
- Run your application that uses the SQL Express or use Server Explorer in Visual Studio to connect to it. this is imporatant because, A User Instances is spawned by the parent instance of SQL Express when an application makes a special connection to the server. by using User Instance=TRUE in the connection string.
- In Management studio connect to the parent instance usually (local)\SQLEXPRESS.
- Open a query Window and execute
-
SELECT
owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances
- User Instances are only accessible through the Shared Memory connection protocol, and are only exposed through a Named Pipe.
-
|
ShervXPS\Shervin Shak
|
\\.\pipe\C3DCB378-EDBB-43\tsql\query
|
alive
|
- Copy the Pipe
- in Managment Studio click on Connect and for server name paste the instance pipe name.
- make sure to use windows Authentication.
Here is a great blog post that goes into more detail http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
this white paper was also very helpful learning about SQLExpress user instances http://msdn2.microsoft.com/en-us/library/bb264564.aspx