EF Core Migration error: Database 'MyDatabaseName' already exists. Choose a different database name

Question

I am running ASP.NET Core 2.1 with EF Core 2.1 application on Windows Server 2016 with SQL Server 2017 Web edition.

At the end of public void Configure(IApplicationBuilder app, ... method in Startup.cs I call context.Database.Migrate();. This applies migrations.
Everything works.

Now I backup database in my development environment in SQL Server 2016, move MyDatabaseName .bak file to server and restore database MyDatabaseName on server and restart IIS site.
When I start application (open browser) I get the following error:

Application startup exception: System.Data.SqlClient.SqlException (0x80131904): Database 'MyDatabaseName' already exists. Choose a different database name.

in line: context.Database.Migrate();. Full error is at the bottom.

If I change MyDatabaseName to MyDatabaseNameX (which doesn't exists) database is created, all migrations are applied, I can reset IIS, application is started. If I restore database I get error already exists.

Same application (exactly the same dll) runs application on development and production environment. That also means database structure is the same.

I need to restore database on production. I am just not sure why context.Database.Migrate() throws error?

Full error:

Application startup exception: System.Data.SqlClient.SqlException (0x80131904): Database 'MyDatabaseName' already exists. Choose a different database name. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Create() at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade) at MyProject.Startup.Configure(IApplicationBuilder app, AppUserManager userManager, IServiceProvider serviceProvider) in C:\GitLab-Runner\builds\7cab42e4\0\web\MyProject\Startup.cs:line 582 --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app) at Microsoft.AspNetCore.Server.IISIntegration.IISSetupFilter.<>c__DisplayClass4_0.b__0(IApplicationBuilder app) at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.b__0(IApplicationBuilder builder) at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication() ClientConnectionId:7f6b84a3-e0ea-42c7-947d-a9cafdaffbfa Error Number:1801,State:3,Class:16 Hosting environment: Production Content root path: C:\WWW\MyProject Now listening on: http://127.0.0.1:24830 Application started. Press Ctrl+C to shut down. Application is shutting down...

Answer 1

This was a nasty one. Database really exists (I did restore it), but problem was that with backup owner of database was also transferred.
User that was owner on localhost do not exists on server. So migrations didn't find database (because it didn't have access to) so it tries to create a new one.

Answer 2

Before start talking about a possibile fix, there's an important thing that we need to understand: the migration pattern is an excellent way to ensure that all the Databases you're working on (and you'll use to connect your app with) will have a consistent and up-to-date structure in any given environment – testing, stage, production, DR and so on; if you choose to use it, the best thing you can do is to stick to the pattern best practices and ensure that the Migrate() method is called whenever you need to.

That said, you might still want to use the Migrate() method solely to create your database on the first run, without having to programmatically (and automatically) keep track of any further migration. If that's the scenario you're living, the best thing you can do is to wrap the Migrate() method inside a conditional block such as the following one:

if (!dbContext.Database.GetService<IRelationalDatabaseCreator>().Exists())
{
    var host = BuildWebHost(args);
    using (var scope = host.Services.CreateScope())
    {
        var dbContext = scope.ServiceProvider.GetService<ApplicationDbContext>();
        var roleManager = scope.ServiceProvider.GetService<RoleManager<IdentityRole>>();
        var userManager = scope.ServiceProvider.GetService<UserManager<ApplicationUser>>();

        // Create the Db if it doesn't exist and applies any pending migration.
        dbContext.Database.Migrate();

        // Seed the Db
        DbSeeder.Seed(dbContext, roleManager, userManager);
    }
    host.Run();
}

That way you will ensure that the Migrate() method will be programmatically executed only if the Database doesn’t exists yet: that would be perfect for testing environments, where you can just drop and recreate the Database everytime without having to worry about losing actual data, and/or for any other context where you prefer to manually update your Database – for example using the dotnet ef powershell command. That’s good for performance and also to avoid the SqlException from the Migrate() method, as it will only run whenever there is no Database to begin with, thus preventing the chance of finding wrong or outdated migrations data.

If you’re curious about what the Exists() method actually does behind the curtains, you can easily check it out by looking at the in the SqlServer.Storage/Internal/SqlServerDatabaseCreator.cs class within the EF Core’s official GitHub repository: you will see that there’s no actual magic – just an attempt to open the connection and either catch the SqlException and return false or return true. Probably not the best thing you can hope to find there, yet still better than nothing (at least it does the job).

In case you need additional info, check out the blog post that I wrote on such issue.