Sunday, August 31, 2014

Entity Framework - Do Not Delete LocalDb !

Entity Framework and LocalDb go together like bread and butter when developing a new application.  However, if you have you ever had a problem with your LocalDb instance manually deleting the .mdf file is probably not the way to go.  

I deleted one of my .mdf files the other day and thought it was strange that I could no longer create a new instance of the database using the default EF conventions.  A brief web search set me straight. This blog tells the whole story.  

The short version is SQL Server maintains a reference to the .mdb files it has created, when you manually delete the file, SQL Server doesn't know the file is gone.  So, don't manually delete the file.  

If you like me have already deleted LocalDb, you may want to still use your Db name, so your looking for the fix.  The fix is in the reference blog post, however for completeness I'll repeat what it says.  Go download the SqlCmd Utility.  Run the following command...

C:\>sqlcmd -S (localdb)\v11.0 -E -d master -Q "DROP DATABASE [myApp]"

You will be presented with an error like the following

Msg 5120, Level 16, State 101, Server User1-PC\LOCALDB#5725A8FF, Line 1
Unable to open the physical file "C:\Users\User1\myApp.mdf". Operating
system error 2: "2(The system cannot find the file specified.)".
File activation failure. The physical file name "C:\Users\User1\myApp_log.ldf"
may be incorrect.

The LocalDb database file will now be correctly unregistered.  If in the future you want to delete your database, use this method instead of a hard delete.  

No comments:

Post a Comment