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.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;
{
DialogResult _result = MessageBox.Show("Do you want to Restore the Database", "Warning!",
MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (_result == DialogResult.No)
{
return;
}
SqlConnection objconnection = new SqlConnection(connectionstring);
ServerConnection con = new ServerConnection(objconnection.DataSource.ToString());
Server server = new Server(con);
try
{
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
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);