Detach database programmatically

Question

I have a database "D:\MDF CONNECTION SAMPLE\BIN\DEBUG\HARMDATABASE.MDF". I am trying to detach or rename it, with this code:

SqlConnection conn = new SqlConnection("Data Source=.\\MSSQLSERVER2008;database=Master;Integrated Security=True;");
SqlCommand cmd = new SqlCommand("", conn);
cmd.CommandText = @"sys.sp_detach_db D:\MDF CONNECTION SAMPLE\BIN\DEBUG\HARMDATABASE.MDF";
conn.Open(); 
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Dispose();

But getting an error:

Incorrect syntax near '\'.

Answer 1

You left out a quote inside the query, and as @KyleHale pointed out - it needs to be the name of the database, not a path to it.

Change:

cmd.CommandText = @"sys.sp_detach_db D:\MDF CONNECTION SAMPLE\BIN\DEBUG\HARMDATABASE.MDF;";

to be:

cmd.CommandText = @"sys.sp_detach_db 'dbName'";

Answer 2

In order to detach a database and at the same time solve the error

Cannot detach the database 'YOUR_DATABASE' because it is currently in use

you can simply use the following code:

    private void DetachDatabase()
    {
        String databaseConnectionString = "Data Source=localhost;MultipleActiveResultSets=True;Integrated Security=True";
        using (SqlConnection sqlDatabaseConnection = new SqlConnection(databaseConnectionString))
        {
            try
            {
                sqlDatabaseConnection.Open();
                string commandString = "ALTER DATABASE YOUR_DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE YOUR_DATABASE SET SINGLE_USER EXEC sp_detach_db 'YOUR_DATABASE'";
                SqlCommand sqlDatabaseCommand = new SqlCommand(commandString, sqlDatabaseConnection);
                sqlDatabaseCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }

Notice that YOUR_DATABASE is sometimes written without the single quotes, and sometimes with the single quotes. Only replace YOUR_DATABASE with the name of your database without .mdf extension and leave the rest of the string as it is...

Thanks to this website: Detach database dropping connections

And this website: SQL Server – How to Detach a Database

By the way, this worked for me on SQL SERVER 2014

Answer 3

How about using SMO?

You need to add a reference to Microsoft.SqlServer.Smo which is available when you have SQL Express or SQL Server installed on your dev machine.

using Microsoft.SqlServer.Management.Smo;

void Detach()
{
  Server smoServer = new Server("MSSQLSERVER2008");
  smoServer.DetachDatabase("HARMDATABASE", False);
}