Database already exist. Choose a Different Name using CreateDatabase()

Question

I got an issue and learned something at the same time....

I created a DBML from an existing server database.

From the DBML I wanted to create local database (an .mdf file). I created the database using DataContext.CreateDatabase("C:\xxxx.mdf") .

Then I decided to delete it (MANUALLY, which is a bad thing evidentally) because when I try to recreate the database with the same name (eventhough the files are deleted), I get the error Database already exist. Choose a Different Name using CreateDatabase()

I tried looking through the registry, no luck... I tried searching the whole hard drive for the file.. no luck.

After googling, I found that you delete a database that was created with CreateDatabase() with DeleteDatabase().... Then you can recreate the database again.

Well problem is, now I still can't recreate the old database because the system thinks the name already exists.

Is there a way to get rid of the reminents of the old databse file the "does not exist"

Solution 1

You need to open master database via server explorer in Visual Studio (Add New Connection + Select master database) then add a New query, type Drop Database xxxx and execute it. You can also use Sql Server Management Studio.

Solution 2

A solution (via here) is to use SSEUtil to detach the existing db:

try
{
    // open a connection to the database for test
}
catch (SystemException ex) // Change exception type based on your underlying data provider
{
    if (ex.Message.ToLower().Contains("already exists. choose a different database name"))
    {
        var match = Regex.Match(ex.Message, "database '(.*)' already exists.", 
           RegexOptions.IgnoreCase);

        if (match.Success)
        {
            String dbFileName = match.Groups[1].Value;
            Process p = new Process();
            p.StartInfo.UseShellExecute = true;
            p.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
            p.StartInfo.FileName = String.Format("{0}/Tools/SSEUtil.exe", 
              Environment.CurrentDirectory);
            p.StartInfo.WorkingDirectory = Environment.CurrentDirectory;
            p.StartInfo.Arguments = String.Format("-d \"{0}\"", dbFileName);

            p.Start();
        }
    }
}

Solution 3

Here's a quick fix for a localDB mess. Just connect to (localdb)\MSSQLLocalDB in SQL Mgmt. Studio/Visual Studio.

Then delete the offender, checking "Close existing connections"

Comments

[DJ Burb]

When I try DeleteDatabase(), I get this: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\xxxx.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share..

[adl]

I encountered the same problem too. Is it possible to do what you suggested programmatically?

[Syed Saad]

+1 I've encountered this problem when programmatically deploying SQL Server databases with Linq to SQL classes. Not sure why the database remains attached after the application exists, however this tool manages to detach the database. If anyone has ideas how to prevent this problem in the first place and therefore remove the need for this solution it would be great. Thanks for the solution by the way.

[Daniel]

In case someone is wondering the same thing, yes, you can do this programatically. Simply connect to the database in your code, then send the appropriate SQL statement for execution (drop database xxxx).