Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

It seems like this is a very popular message with SQL Server 2005 Express (if you google it, you'll find a lot of resources), and is often a hard one to figure out.  However, there are some ways that you can get to the bottom of it.  For instance, whenever SQL Server 2005 express tries to attach the database, a log is generated stating the process that is occurring.  You can get to this log file by going to this folder (note: replace <User> with your user name):

XP: C:\Documents and Settings\<User>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
VISTA: C:\Users\Brian\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

At any rate, check out the error.log, and see the names of the databases that were erroring.  For me, it happened because the SQL Server Developer instance didn't have certain databases in the MSSQL.1\MSSQL\Template Data folder, but had them in the \Data folder.  Simply copying them over solved the problem for me.

Another problem you may experience is that the SQLServer2005$MSSqlUser$<Server Name>$SQLEXPRESS (replace <Server Name> with the name of your server) did not have permissions to the folder listed above; I added it in case of permissions issues.  This may not be the issue, but take note that you may need to grant access for this account in some way.

I hope this helps someone solve this problem.


No Comments