Configure single user mode to restore backup

Question

I have the following function:

public void RestoreDatabase(String databaseName, String backUpFile, String serverName, String userName, String password) { SqlConnection.ClearAllPools(); ServerConnection connection = new ServerConnection (serverName, userName, password); Server sqlServer = new Server(connection); Restore rstDatabase = new Restore(); rstDatabase.Action = RestoreActionType.Database; rstDatabase.Database = databaseName; BackupDeviceItem bkpDevice = new BackupDeviceItem (backUpFile, DeviceType.File); rstDatabase.Devices.Add(bkpDevice); rstDatabase.ReplaceDatabase = true; rstDatabase.SqlRestore(sqlServer); } 

I tried to set the database to single user mode first before restoring the backup. I tried this code:

  private string singleUserCmd = "alter database db-name set SINGLE_USER"; private string multiUserCmd = "alter database db-name set MULTI_USER"; private void SetSingleUser(bool singleUser, SqlConnectionStringBuilder csb) { string v; if (singleUser) { v = singleUserCmd.Replace("db-name", csb.InitialCatalog); } else { v = multiUserCmd.Replace("db-name", csb.InitialCatalog); } SqlCommand cmd = new SqlCommand(v, new SqlConnection (csb.ToString())); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); } finally { cmd.Connection.Close(); } } 

It seems that the problem is that one user mode is used for this connection so that no one does anything. I need this to be for a connection that restores a backup.

Answer 1

Recovery should occur in a connection that puts the database in single-user mode, so why not force the SetSingleUser function to return the open SqlConnection on which it was executed, and then enable the recovery code and use the same open connection.

 private string singleUserCmd = "alter database db-name set SINGLE_USER"; private string multiUserCmd = "alter database db-name set MULTI_USER"; private SqlConnection SetSingleUser(bool singleUser, SqlConnectionStringBuilder csb) { string v; if (singleUser) { v = singleUserCmd.Replace("db-name", csb.InitialCatalog); } else { v = multiUserCmd.Replace("db-name", csb.InitialCatalog); } SqlConnection connection = new SqlConnection(csb.ToString()); SqlCommand cmd = new SqlCommand(v, connection); cmd.Connection.Open(); cmd.ExecuteNonQuery(); return connection; } 

Answer 2

Duckworth's answer is quite correct. but you can also do it like this:

 using System; using System.Data.SqlClient; using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; public class DatabaseRestoreHelper { private const string _SingleUserCmd = "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE"; private const string _MultiUserCmd = "ALTER DATABASE {0} SET MULTI_USER"; public static void RestoreDatabase(string connectionString, string backupSetPath, bool verify) { SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(connectionString); string database = cb.InitialCatalog; cb.InitialCatalog = "master"; SqlConnection sqlConnection = new SqlConnection(cb.ConnectionString); ServerConnection serverConnection = new ServerConnection(sqlConnection); try { //Make Database Single User serverConnection.ExecuteNonQuery(String.Format(_SingleUserCmd, database)); Server server = new Server(serverConnection); Restore restore = new Restore(); BackupDeviceItem destination = new BackupDeviceItem(backupSetPath, DeviceType.File); restore.Action = RestoreActionType.Database; restore.Database = database; restore.Devices.Add(destination); restore.ReplaceDatabase = true; if (verify) { string errorMessage; if (!restore.SqlVerify(server, out errorMessage)) { throw new Exception(errorMessage); } } restore.SqlRestore(server); } catch { throw; } finally { //Make Database Multi User serverConnection.ExecuteNonQuery(String.Format(_MultiUserCmd, database)); serverConnection.Disconnect(); } } public static void BackupDatabase(string connectionString, string backupSetPath, bool verify) { SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder(connectionString); string database = cb.InitialCatalog; cb.InitialCatalog = "master"; SqlConnection sqlConnection = new SqlConnection(cb.ConnectionString); ServerConnection serverConnection = new ServerConnection(sqlConnection); try { //Make Database Single User serverConnection.ExecuteNonQuery(String.Format(_SingleUserCmd, database)); Server server = new Server(serverConnection); Backup backup = new Backup(); BackupDeviceItem destination = new BackupDeviceItem(backupSetPath, DeviceType.File); backup.Action = BackupActionType.Database; backup.Database = database; backup.Devices.Add(destination); backup.SqlBackup(server); if (verify) { Restore restore = new Restore(); restore.Action = RestoreActionType.Database; restore.Database = database; restore.Devices.Add(destination); restore.ReplaceDatabase = true; string errorMessage; if (!restore.SqlVerify(server, out errorMessage)) { throw new Exception(errorMessage); } } } catch { throw; } finally { //Make Database Multi User serverConnection.ExecuteNonQuery(String.Format(_MultiUserCmd, database)); serverConnection.Disconnect(); } } }