Error connecting to localdb after .mdf and .ldf files are deleted.

By Mirek on (tags: localdb, categories: infrastructure)

Localdb database is very flexible and comfortable to use. Although its purpose is rather to serve a development database storage, its also possible to use it in a production. Unfortunately using it in, for instance, Click-Once deployed application generates some strange problems. One of those mystery problems I faced recently.

Using localdb in production has some limitations. For example it can be used on local machine only, since localdb does not allow remote connections. Its simple installation, no configuration required and support for Click-Once prerequisites, makes this database a perfect replacement for SQL Compact Edition in Click-Once deployed applications that uses local database.

The problem I want to describe now is caused by deleting the .mdf and .ldf files that belong to the localdb database that our application uses. The deletion can be either made manually or automatically by application uninstaller. The second case occurs when we force localdb to store our database files inside application related directories, by specifying the database file in connection string similar to this

Data Source=(localdb)\v11.0;Integrated Security=true;
Initial Catalog=myApp;AttachDbFileName=|DataDirectory|\myApp.mdf

When we uninstall myApp the Click-Once setup will delete the |DataDirectory| with all its content including .mdf and .ldf files.
Apparently the localdb engine somehow keeps the registration of databases internally and when you delete the underlying files the registration stays. In my case it revealed when I tried to run the application once again after clean installation. I get following exception when Entity Framework tries to connect to the database

Cannot open database "myApp" requested by the login. The login failed.

Strange, because first of all I use Trusted Security and secondly Entity Framework normally creates the database if one does not exist. Well this is caused by the fact that EF tries to find an existing database with name myApp in the localdb. Localdb has that name registered in its database list, but because the database files are missing the connection fails.Fortunately there are at least two solutions for this problem.

  1. The trivial solution is to change the name of database (Initial Catalog) in the connection string so the EF will create new database with different name. Files will be recreated correctly.
  2. For those who prefers things to be clean we can use sqlcmd Utility to clear the registration of corrupted database. To do that we must run a command line batch

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

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.

C:\>

For short explanation, here we connect to (localdb) instance and with use of master database and trusted connection –E we run a sql query DROP DATABASE [myApp]. Here comes the strangest thing with localdb. However it says that cannot connect to the database because cannot find the .mdf and .ldf files, it somehow properly ‘unregisters’ the database and fixes the problem.

By the way the second solution is at the same time the right way to delete the database instead of roughly deleting its files Puszczam oczko

 

Greets