How To Restore SQL Server Database using C# even if it's in use

Question

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);
                }
            }
        }

nekno · Accepted Answer · 2013-12-14 20:06:44Z

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);
            }

        }
    }

That was gr8 answer.. thank you very much!!! I will learn about single user mode by googling
Yes single_user mode is supposed to be all you need but this is the one-stop solution for all scenarios. Thank you! (More upvotes!)

Ehsan · Accepted Answer · 2013-12-14 06:14:19Z

5

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

answered Dec 14, 2013 at 6:14

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

Comments

Ben Thul · Accepted Answer · 2013-12-14 14:17:40Z

2

In SMO, there's a KillAllProcesses method on the Server object. It takes a database as the sole argument.

answered Dec 14, 2013 at 14:17

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Collectives™ on Stack Overflow

How To Restore SQL Server Database using C# even if it's in use

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related