ReBuildAll Blog
Thoughts (mostly) on .NET development

I was trying to find a solution to a problem with LINQ and its CreateDatabase() call (see details in another blog post). Then I bumped into the following problem.

When I tried attaching a database into an SQL Server Express User Instance that existed before (the same database under the same path), I received an exception. I actually manually deleted the .MDF and .LDF files from disc, and then tried to recreate them programatically. The exception informed me the database file already existed. Although I have deleted it myself.

The exception said: Database 'path_to_database' already exists. Choose a different database name.. Which was very strange.

Reading more about SQL Server Express User Instances, I found out that they are more complicated than I thought. What actually happens is that SQL Server Express copies the master and msdb databases under the user's directory (who runs the user instance). It then starts the user instance (sqlservr.exe) under the user account. When you want to use a database in a user instance, it actually attaches the database file to the user instance - just as you would attach a database to a regular SQL Server instance. It just happens behind the scenes.

This creates trouble of course, because the database is registered somewhere, and you shouldn't just delete the files. Which I did. And that is why I got the error message.

There is some information about a tool that you can use to connect to a user instance and execute commands (detach maybe?). I found it too complicated. (But if you are interested, visit this MSDN page for more information!)

Rather, I killed my user instance process (Task Manager, kill sqlservr.exe). Then proceeded to delete my user instance directory. For Windows 7, this would be:

c:\Users\username\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

I then restarted my application (which called CreateDatabase()), and it worked. It recreated the above directory, and everything was fine.

I have to admit that this seems like a bit of a drastic solution, but it sure is faster than connecting to the instance and issuing SQL commands by hand

And it should not have any side effects, because all user instance databases will just get attached automatically the next time you use them.