19

I'm following this guide illustrating a code-first approach with Entity Framework core. Migrations were functioning correctly until, at some point, I deleted my .mdf file. Since then, executing Update-Database (to apply my migration) throws the following error: Database 'DatabaseName' already exists. Choose a different database name.

Where exactly is this database? How can I remove it permanently?

According to this answer, I need to detach my database from Sql Server, but I'm not sure how to do that now. In Sql Server Management studio, If I execute sp_detach_db DatabaseName it throws error The database 'DatabaseName' does not exist. Supply a valid database name.

Thanks in advance.

UPDATE I see I can also reproduce this database already exists error if I have the database attached in SQL Server Management Studio, and execute the Update-Database command. After I close the management studio, the migration applies without this error. Very confusing to me.

Community
  • 1
  • 1
Stephen Paul
  • 37,253
  • 15
  • 92
  • 74

9 Answers9

35

Seems like an instance of LocalDB is still running in the background. Execute the following commands in the console to stop and delete the instance.

sqllocaldb stop
sqllocaldb delete

The Update-Database command should now have no problems.

chaosifier
  • 2,666
  • 25
  • 39
10

Please look at the SQL Server Object Explorer (Visual Studio -> View -> SQL Server Object Explorer). If it contains the 'DatabaseName' database then please try to delete it.

Alexander Zinoviev
  • 144
  • 1
  • 5
1

If you create your database with SQL (meaning DB already exists), put the line DROP DATABASE [databaseName] at the beginning of the file databaseName.sql

This will delete the whole DB and its definition (schema), now you can start with creating your new DB.

roottraveller
  • 7,942
  • 7
  • 60
  • 65
sosay
  • 11
  • 1
  • This worked like a charm for me when I was working with importing schema into RDS by scripts. I had to run the scripts multiple times to figure out which errors were being thrown by RDS since it doesn't allow some configuration and permissions. – Lacrosse343 Aug 16 '22 at 20:18
1

Another guess.. if you have restored the database then your user login will not work anymore because of the orphan. Try to remove user and create it again. you will get the same error if your migration cannot access the database.

akd
  • 6,538
  • 16
  • 70
  • 112
  • I restored a db from prod in my local machine and I got the error. I removed all users from that and then my existing user as dbowner to db. problem solved. tnx – Bashir Momen Jul 08 '21 at 09:26
0

It might be a local DB. Try connecting to your local DB

(LocalDb)\MSSQLLocalDB

And seeing if the DB is located there. You may need to delete it from SSMS

rborob
  • 38
  • 1
  • 4
0

Try to modify the number code of 'xxxxx-20170604092712' in Web.config file,

and then 'update-database' again. This worked for me.

Mystic Lin
  • 365
  • 4
  • 15
0

I restore my database to another server, To fix this I had to remove from the DB under Security the previous users and create them again

Juan Felipe V V
  • 1
0

I am using MSSQLLocalDB. I've encountered this issue after restoring the backup of a DB using the Azure Data Studio.

When I connected using Microsoft SQL Management Studio, I've noticed that my DB was in a SINGLE_USER mode (I have not noticed it in the Azure Data Studio).

Changing the mode to MULTIPLE_USER fixed the issue for me:

enter image description here

Marta
  • 326
  • 4
  • 11
0

In my case, I wanted to run migrations straight after starting the SQL server on linux in docker container. The thing was that the SQL server wasn't yet fully started. So after 30s the migrations went just fine.

cryss
  • 4,130
  • 1
  • 29
  • 34