-1

I deleted manually sql database file(myDB.mdf and myDB_log.ldf) When I want to create database with same name (myDB) I get the following error message.

System.Data.SqlClient.SqlException (0x80131904):Database 'myDB' already exists. Choose a different database name.

my code:

String str;
SqlConnection Conn = new SqlConnection("Server=.\\SQLEXPRESS;Integrated security=true;User Instance=True;database=master");
str = "CREATE DATABASE myDB " + 
      "ON PRIMARY " + "( NAME = N'myDB', FILENAME = N'" + Application.StartupPath + "\\myDB.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )" + 
      "LOG ON" + "( NAME = N'myDB_log', FILENAME = N'" + Application.StartupPath + "\\myDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)";
SqlCommand Command = new SqlCommand(str, Conn);

how fix this by c# script?

koorosh
  • 1
  • 1
  • 7
    Deleting the files will not delete the details of the database in the server, you need to issue a `DROP DATABASE ...` statement – Alex K. Nov 26 '14 at 11:48
  • You shouldn't delete a SQL database by just deleting the data and log files, you should use `DROP DATABASE`, then this will remove it from sys.databases. – Ben Robinson Nov 26 '14 at 11:51
  • physical database not exists but logical name exists, i think – koorosh Nov 26 '14 at 13:48

2 Answers2

0

You should not delete database files directly because they are referenced in the master database in SQL Server. If you can restore the files, do this and then delete the database from within SQL Server Management Studio or otherwise you will need to manually delete the database entries in the master database tables!

Lukos
  • 1,826
  • 1
  • 15
  • 29
  • i deleted database file by shift+delete now i want to create database with same name of deleted database. how fix by script c# in visual studio – koorosh Nov 26 '14 at 12:29
  • You could run DROP DATABASE myDB; as a SQL query but I don't know what will happen if the files are missing. – Lukos Nov 26 '14 at 12:44
  • my database not in list of database in sql server management studio. i created database in c# and attached it by "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\myDB.mdf;Integrated Security=True;User Instance=True" – koorosh Nov 26 '14 at 13:00
  • You should still be able to see it but you might need to choose a different instance to connect to in the SQL server management studio connect dialog. Can't remember the exact syntax but possibly connect to (local)/SQLEXPRESS – Lukos Nov 26 '14 at 13:04
0

You may try to use to resolve the problem

EXEC sp_detach_db 'dbName', 'true';

the above said sp_detach detaches the database with skipchecks set to true

Muhammad Umair
  • 1
  • 3