How To Restore SQL Server Database using C# even if it's in use
Solution 1
I've found killing all processes for the database, setting single user mode, and then detaching the database is effective and can be done with SMO. All three steps were necessary to cover different scenarios, though I couldn't tell you off hand what those are. Theoretically, only placing the DB in single-user mode is necessary.
// 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);
}
}
}
Solution 2
Well you need to close all active connections before beginning your process of restoring. As it will fail if any of the user is connected to your database.
You can achieve that by using the following script and executing it before restore.
use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--do you stuff here
ALTER DATABASE YourDatabase SET MULTI_USER
Solution 3
In SMO, there's a KillAllProcesses method on the Server object. It takes a database as the sole argument.
![VARUN NAYAK](https://i.stack.imgur.com/lqo1x.jpg?s=256&g=1)
VARUN NAYAK
Updated on June 25, 2022Comments
-
VARUN NAYAK about 2 years
I'm using this function for restoring a SQL Server database.
It works perfectly, but my problem is when I am using this function, I have to make sure that the SQL Server connection is closed in my application.
If a SQL Server connection is open in other window this function will not work.
So, how can I restore my database even if a SQL Server connection is open in another window?
I mean is there any way to lock SQL Server during my restore 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); } } }