I was working on a proof-of-concept and needed a data import for some sample code into SQL Server express. Why not just use the regular edition of SQL Server? The folks who wrote the sample code probably didn't want to assume that everyone had a regular SQL Server instance running in their dev environment and didn't want to exclude anyone from using their code. It so happens that I am running a regular SQL Server instance on my machine, but I wanted to accurately repro the setup for for the sample code based the provided instructions, which called for SQL Server express. I probably could have just cheated and imported the database into my regular SQL Server instance, but that would have also introduced more variables into the equation to validate my POC than I was willing to deal with.
Every time I tried to access the imported SQL data, I kept getting this error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance
I checked the network stack, security permissions, everything. As it turns out, the SQL Server express persists some files in your app data directory that don't get cleaned out when you uninstall and reinstall. It's a problem when you attempt to spin up a user instance because a file already exists in your app data directory that SQL Server express is attempting to re-create. It might have been a bit easier to troubleshoot if it told you about the file it failed on in the initial error message. Instead, it just reports on the failed instance.
Solution? Delete the SQLEXPRESS folder under C:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\ so that SQL Server express can re-create the files. If you've got some things in there that you want keep using, you might have to do more precise surgery and find the offending files. In my case, I didn't.
Did I figure this one out all by my lonesome? Not entirely. I still had to dig through the error logs to find the problem, but I'll give credit where credit is due for the solution:
