Restore SQL Database using C#

Question

I am trying to restore sql databse using C# code.Backup is working fine. But when the restore the db it gives me an error. I am using the Microsoft.SqlServer.Management.Smo; for this operation. The error is {"System.Data.SqlClient.SqlError: RESTORE cannot process database 'TempDb' because it is in use by this session. It is recommended that the master database be used when performing this operation."}

In several posts it says set the database to the master. I also tried that.But it gives me the same error. Connection string:connectionString = @"server=(local);Initial Catalog=Master;Integrated Security=True;";

My code is as follows:

openFileDialog1.ShowDialog();
        string databaseName = "TempDb";
        Restore sqlRestore = new Restore();

        BackupDeviceItem deviceItem = new BackupDeviceItem(openFileDialog1.FileName, DeviceType.File);
        sqlRestore.Devices.Add(deviceItem);
        sqlRestore.Database = databaseName;

        DataConnection dataConnection = new DataConnection();
        ServerConnection connection = new ServerConnection(dataConnection.DataBaseConnection);
        Server sqlServer = new Server(connection);

        Database db = sqlServer.Databases[databaseName];
        sqlRestore.Action = RestoreActionType.Database;
        String dataFileLocation = db.FileGroups[0].Files[0].FileName;
        String logFileLocation = db.LogFiles[0].FileName;
        db = sqlServer.Databases[databaseName];
        RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);


        sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
        sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName + "_log", logFileLocation));
        sqlRestore.ReplaceDatabase = true;
        sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
        sqlRestore.PercentCompleteNotification = 10;
        sqlRestore.PercentComplete += new PercentCompleteEventHandler(sqlRestore_PercentComplete);
        sqlRestore.SqlRestore(sqlServer);
        db = sqlServer.Databases[databaseName];
        db.SetOnline();
        sqlServer.Refresh();

Take a look at Cleaning up after calls to SMO Server and Database. — Joshua Drake
– Joshua Drake, Commented May 17, 2012 at 17:00
When we restore Database using SQl server management studio we can chose the back up set. Using this code its restore only the first option.Can I configure that. — udaya726
– udaya726, Commented May 18, 2012 at 13:52
Possibly... you should post that as a new question, after checking for existing similar ones. You did a good job of showing your code here, and doing so on the new question should prove helpful. — Joshua Drake
– Joshua Drake, Commented May 18, 2012 at 13:56

Mojtaba · Accepted Answer · 2013-06-28 13:00:20Z

2

this code For Restore Database,Please Type Code In C#:

SqlConnection ObjConnection=new SqlConnection("Your Connection String");
ObjConnection.Open();
                SqlCommand ObjCommand = new SqlCommand();
                ObjCommand.Connection = ObjConnection;
                ObjCommand.CommandText = "Use Master   ALTER DATABASE YourDatabaseName SET                 OFFLINE WITH ROLLBACK IMMEDIATE  " +
                "Restore Database YourDatabaseName From Disk='" + LblPath.Text + "'" +
                "   ALTER DATABASE YourDatabaseName SET ONLINE WITH ROLLBACK IMMEDIATE";
                ObjCommand.CommandType = CommandType.Text;
                ObjCommand.ExecuteNonQuery();
Help:LblPath.Text is a Label Control Contain Path Backup Database You!
Mojtaba From IRAN

answered Jun 28, 2013 at 13:00

Mojtaba

412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sridhargt · Accepted Answer · 2012-09-03 07:54:18Z

It looks like your database is still in use by other logins. try taking it to single user mode before restorations, it really helps. Make sure that you use the same connection object to take the database to single user mode, then restoration and then to bring back it to multi user mode.

you could try this,

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE", connection);

cmd.Connection.Open();
cmd.ExecuteNonQuery();

return connection; //to use the same connection for restore activity and setting it to multi user mode again


//up on completion of restore activity, take the database to multi user mode.
//ALTER DATABASE <database name> SET MULTI_USER

Lemon Kazi · Accepted Answer · 2015-09-17 06:36:17Z

This code is for restore database

var conString = System.Configuration.ConfigurationManager.ConnectionStrings["CONSTRING"];
string strConnString = conString.ConnectionString;
SqlConnection cs = new SqlConnection(strConnString);

        try
        {
            cs.Open();
            String sqlquery = "Use Master ALTER DATABASE databasename SET OFFLINE WITH ROLLBACK IMMEDIATE RESTORE DATABASE databasename FROM DISK ='" + txtRestoreFileLoc.Text + "' ALTER DATABASE databasename SET ONLINE WITH ROLLBACK IMMEDIATE";
            SqlCommand cmd = new SqlCommand(sqlquery, cs);
            cmd.ExecuteNonQuery();
            cs.Close();
            cs.Dispose();
            MessageBox.Show("restore complete");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);

        }

Collectives™ on Stack Overflow

Restore SQL Database using C#

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related