title: Drop/detach database from C#
body: <p>I have a program that does a lot of configurations on the executing system, sharing folders, checking firewalls, looking for orphaned databases and much more.<br>
  One thing it does is that it looks for databases where the MDF file has been deleted. I want the program to detach or drop the database with the missing MDF. The program uses SMO for database operations.</p>
  
  <p>I've tried the following</p>
  
  <pre><code>string sqlCommand = string.Format("DROP DATABASE [{0}]", databasename); 
  server.KillAllProcesses(databasename);
  server.ConnectionContext.ExecuteNonQuery(sqlCommand);
  </code></pre>
  
  <p>and</p>
  
  <pre><code>server.DetachDatabase(databasename, false);
  </code></pre>
  
  <p>and</p>
  
  <pre><code>string dbQuery = string.Format("ALTER DATABASE \"{0}\" SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE \"{0}\" SET SINGLE_USER EXEC sp_detach_db '{0}'", databasename);
  server.ConnectionContext.ExecuteNonQuery(dbQuery);
  </code></pre>
  
  <p>No matter what I do to try to drop or detach the database I get an exception:</p>
  
  <blockquote>
    <p>Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Unable to open the physical file "masked file name". Operating system error 2: "2(The system cannot find the file specified.)".
           vid Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
           vid Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)</p>
  </blockquote>
  
  <p>Is there any other way to do this programmatically or am I going about things the wrong way?</p>