How to restore SQL Server database using C #, even if it is used

Question

I use this function to restore a SQL Server database.

This works fine, but my problem is that when I use this function, I have to make sure that the SQL Server connection is closed in my application.

If the connection to SQL Server is open in another window, this function will not work.

So, how can I restore my database even if the SQL Server connection is open in another window?

I mean, is there a way to lock SQL Server during my recovery function?

private void btnRestore_Click(object sender, EventArgs e) { // If there was a SQL connection created try { if (srvSql != null) { saveBackupDialog.Title = "Restore Backup File"; saveBackupDialog.InitialDirectory = "D:"; // If the user has chosen the file from which he wants the database to be restored if (openFD.ShowDialog() == DialogResult.OK) { Thread oThread = new Thread(new ThreadStart(frmWaitShow)); oThread.Start(); // Create a new database restore operation Restore rstDatabase = new Restore(); // Set the restore type to a database restore rstDatabase.Action = RestoreActionType.Database; // Set the database that we want to perform the restore on rstDatabase.Database = cmbDatabase.SelectedItem.ToString(); // Set the backup device from which we want to restore, to a file BackupDeviceItem bkpDevice = new BackupDeviceItem(openFD.FileName, DeviceType.File); // Add the backup device to the restore type rstDatabase.Devices.Add(bkpDevice); // If the database already exists, replace it rstDatabase.ReplaceDatabase = true; // Perform the restore rstDatabase.SqlRestore(srvSql); oThread.Suspend(); MessageBox.Show("DataBase Restore Successfull"); } else { // There was no connection established; probably the Connect button was not clicked MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } } 

Answer 1

I found that I killed all the processes for the database, set up single-user mode, and then detaching the database is efficient and can be done using SMO. All three steps were necessary to cover the various scenarios, although I could not tell you what it is. Theoretically, only the placement of the database in single-user mode is required.

 // Kill all processes sqlServer.KillAllProcesses(restore.Database); // Set single-user mode Database db = sqlServer.Databases[restore.Database]; db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single; db.Alter(TerminationClause.RollbackTransactionsImmediately); // Detach database sqlServer.DetachDatabase(restore.Database, false); 

In your method:

 private void btnRestore_Click(object sender, EventArgs e) { // If there was a SQL connection created try { if (srvSql != null) { saveBackupDialog.Title = "Restore Backup File"; saveBackupDialog.InitialDirectory = "D:"; // If the user has chosen the file from which he wants the database to be restored if (openFD.ShowDialog() == DialogResult.OK) { Thread oThread = new Thread(new ThreadStart(frmWaitShow)); oThread.Start(); // Create a new database restore operation Restore rstDatabase = new Restore(); // Set the restore type to a database restore rstDatabase.Action = RestoreActionType.Database; // Set the database that we want to perform the restore on rstDatabase.Database = cmbDatabase.SelectedItem.ToString(); // Set the backup device from which we want to restore, to a file BackupDeviceItem bkpDevice = new BackupDeviceItem(openFD.FileName, DeviceType.File); // Add the backup device to the restore type rstDatabase.Devices.Add(bkpDevice); // If the database already exists, replace it rstDatabase.ReplaceDatabase = true; // Kill all processes srvSql.KillAllProcesses(rstDatabase.Database); // Set single-user mode Database db = srvSql.Databases[rstDatabase.Database]; db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single; db.Alter(TerminationClause.RollbackTransactionsImmediately); // Detach database srvSql.DetachDatabase(rstDatabase.Database, false); // Perform the restore rstDatabase.SqlRestore(srvSql); oThread.Suspend(); MessageBox.Show("DataBase Restore Successfull"); } else { // There was no connection established; probably the Connect button was not clicked MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } } 

Answer 2

You must close all active connections before starting the recovery process. Since it will not work if any user is connected to your database.

You can achieve this by using the following script and executing it before restoring.

 use master ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE --do you stuff here ALTER DATABASE YourDatabase SET MULTI_USER 

Answer 3

In SMO, there is a KillAllProcesses method on the Server object. A database is required as the only argument.