May 18, 2013

Cannot attach the file '.mdf' as database in MVC


While doing database update using code-first migrations in ASP.Net MVC, came across the strange exception and details are as follows,

Issue back ground details,
1. Manually deleted auto created ".mdf" file from App_Data folder using Visual Studio.
2. Executed update-database in package manager console. Then got the below exception,

System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file 'E:\Backup\Practice\MVC4\DotNetExamples\DotNetExamples\App_Data\DotnetExamples.mdf' as database 'DotnetExamples'.

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

   at System.Data.SqlClient.SqlConnection.Open()

   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)

   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)

   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)

   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)

   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)

   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)

   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)

   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)

   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)

   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()

   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()

ClientConnectionId:7c44a645-a831-418e-b8e6-88232006e97a
 
 No clue about this, how to resolve and after spending lot of time searching on web, came across the following solution. Keeping this for my future reference and it might help for others for same type of problem,

Solution:


If you delete the DB file, it still stays registered with SqlLocalDB. Sometimes it fixes it by deleting DB. We can do this from the command line.


Open the "Developer Command Propmpt for VisualStudio" under your "Start/Programs menu->All Programs->Visual Studio 2012->Visual Studio Tools"



    Run the following commands:

    sqllocaldb.exe stop v11.0

    sqllocaldb.exe delete v11.0


Now execute "update-database" command from package manager console and it will create database for you without any obstacles.

If you are using SQL LocalDb, don’t ever delete the physical .mdf and .log files for your database without going through the SQL Server Object Explorer in Visual Studio or in SQL Server Management Studio. If you delete the files only, you end up with an error like mentioned above.