Question

polachan on Thu, 08 Sep 2016 13:04:01


I have written the following code to restore database but now it is not working due to the file is in use. I want to rewrite the program as  to connect different database  like 'master'   and  then   restore the application config connected database.

Please anybody can help how can connect another database and restore  working database in my script

the following is my code 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using MiddleTier;
using System.Configuration;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
 private void btnRestore_Click(object sender, EventArgs e)
        {

            DialogResult _result = MessageBox.Show("Do you want to Restore the Database", "Warning!",
                                   MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
            if (_result == DialogResult.No)
            {
                return;
            }
            string connectionstring = ConfigurationManager.ConnectionStrings["MyConnection"].ToString();
            SqlConnection objconnection = new SqlConnection(connectionstring);
            ServerConnection con = new ServerConnection(objconnection.DataSource.ToString());
            Server server = new Server(con);
            try
            {
                Database db = server.Databases["kmInvoice"];
                Restore _dataBase = new Restore();
                _dataBase.Database = db.Name;             
                _dataBase.Action = RestoreActionType.Database;
                _dataBase.Devices.AddDevice(@"C:\MyData\mydatabase.bak", DeviceType.File);
                _dataBase.ReplaceDatabase = true;
                _dataBase.SqlRestore(server);
                _dataBase.NoRecovery = false;
                MessageBox.Show("Restore Completed");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error" + ex.ToString());
            }
            finally
            {

            }

        }

After running the program, the message will get "Restore Completed" and when I go to database , there  is no  restore have been done. so the code is not working . Please help me and it would be very appreciate

Regards

pol


 


polachan


Sponsored



Replies

CoolDadTx on Thu, 08 Sep 2016 15:46:06


Try setting the NoRecovery to true before you call SqlRestore.  I'm not convinced your restore is actually restoring anything.

Michael Taylor
http://www.michaeltaylorp3.net

Olaf Helper on Thu, 08 Sep 2016 17:22:29


I want to rewrite the program as  to connect different database  like 'master'


Then use a different connection then "MyConnection", where the database ("Initial Catalog") to connect to is defined.

Santosh Chandel on Thu, 08 Sep 2016 17:58:57


Also, the job\code owner you wrote, are carrying sysadmin access to backup\restore on the dbs, while job is executing.

you can connect to other db and attempt to restore, however the existing connection needs to be removed  or it will give error that DB is in used.

case: When full backup is there:

The flow would be: Kill all existing connections for targeted DB name before restoring, take DB into single user mode, restore with recovery, bring db into multi-user mode and give any access if you think DB will not have to get your application connect to.

When Diff backup is there

The flow would be: Kill all existing connections for targeted DB name before restoring, take DB into single user mode, restore full backup with no-recovery, restore diff backup with recovery, bring db into multi-user mode and give any access if you think DB will not have to get your application connect to.

 

Ben Miller - DBAduck on Thu, 29 Sep 2016 00:15:41


You can simply run server.KillAllProcesses("your_database_name"); before you do the _dataBase.SqlRestore(server) command to kill all the connections.

server.KillAllProcesses(db.Name);
_dataBase.SqlRestore(server);