title: Unable to restore SQL database, exclusive access could not be obtained (single user mode)
body: <p>I am writing a simple database backup and restore routine for an application. I can backup my database without issues, however when I restore is I am unable to gain exclusive access to my database. </p>
  
  <p>I am trying all the combinations of fixes on SO, putting in single user mode, taking it offline then placing it back only with no success. 
  I can successfully restore the database within studio manager (express)</p>
  
  <p>This method is the only connection to the SQL server at the time, so I don't understand why I can't perform the restore. </p>
  
  <p>Appreciate the help to point out where the issue may be. </p>
  
  <pre><code>internal void RestoreDatabase(string databaseFile)
          {
              //get database details
              var databaseConfiguration = new DatabaseConfiguration().GetDatabaseConfiguration();
  
              try
              {
                  //construct server connection string
                  var connection = databaseConfiguration.IsSqlAuthentication
                                       ? new ServerConnection(databaseConfiguration.ServerInstance,
                                                              databaseConfiguration.SqlUsername,
                                                              databaseConfiguration.SqlPassword)
                                       : new ServerConnection(databaseConfiguration.ServerInstance);
  
                  //set database to single user and kick everyone off
                  using (
                      var sqlconnection =
                          new SqlConnection(new DatabaseConfiguration().MakeConnectionString(databaseConfiguration)))
                  {
                      sqlconnection.Open();
                      using (
                          var sqlcommand = new SqlCommand("ALTER DATABASE " + databaseConfiguration.DatabaseName + " SET Single_User WITH Rollback IMMEDIATE",
                                                          sqlconnection))
                      {                       
                          sqlcommand.ExecuteNonQuery();                        
                      }
                      using (
                          var sqlcommand = new SqlCommand("ALTER DATABASE " + databaseConfiguration.DatabaseName + " SET OFFLINE",
                                                          sqlconnection))
                      {
                          sqlcommand.ExecuteNonQuery();
                      }
                      using (
                          var sqlcommand = new SqlCommand("ALTER DATABASE  " + databaseConfiguration.DatabaseName + "  SET ONLINE",
                                                          sqlconnection))
                      {
                          sqlcommand.ExecuteNonQuery();
                      }
                      sqlconnection.Close();
                  }
  
                  //setup server connection and restore
                  var server = new Server(connection);
                  var restore = new Restore();
                  restore.Database = databaseConfiguration.DatabaseName;
                  restore.Action = RestoreActionType.Database;
                  restore.Devices.AddDevice(databaseFile, DeviceType.File);
                  restore.ReplaceDatabase = true;
                  restore.Complete += Restore_Complete;
                  restore.SqlRestore(server);
              }
              catch (Exception ex)
              {
                  //my bad
                  restoreDatabaseServerError(ex.InnerException.Message, EventArgs.Empty);
              }
              finally
              {
                  //set database to multi user
                  using (
                      var sqlconnection =
                          new SqlConnection(new DatabaseConfiguration().MakeConnectionString(databaseConfiguration)))
                  {
                      sqlconnection.Open();
                      using (
                          var sqlcommand = new SqlCommand("ALTER DATABASE " + databaseConfiguration.DatabaseName + " SET Multi_User",
                                                          sqlconnection))
                      {
                          sqlcommand.ExecuteNonQuery();
                          sqlcommand.Dispose();
                      }
                      sqlconnection.Close();
                  }
              }
  
          }
  </code></pre>