Dear friends,
I am developing a windows application in which i want to develop a restore utility in which i have a button and on click of that button i am creating a new database and restoring the given backup file.
my code is as follows
private void Restore_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=.\SQLExpress;Integrated Security=True");
con.Open();
DateTime nwDt = DateTime.Now;
string strWinPath = Environment.GetEnvironmentVariable("windir");
string strTemp = strWinPath.Substring(0, 1);
StringBuilder sbOGDatabasePath = new StringBuilder(@"X:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\");
sbOGDatabasePath.Replace('X', strTemp[0], 0, 1);
string path = Environment.CurrentDirectory.ToString();
string frmt = "DdMMMyyThTmm";
string chngdt = nwDt.ToString(frmt);
string sql = "CREATE DATABASE mydb2" + chngdt + "";
string clientMDFPath = sbOGDatabasePath.ToString() + "mydb2" + chngdt + ".mdf";
string clientLDFPath = sbOGDatabasePath.ToString() + "mydb2" + chngdt + ".ldf";
SqlCommand cmd = new SqlCommand(sql, con);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Database created");
con.Close();
con.Open();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString());
}
con.Close();
SqlConnection Connection = new SqlConnection(@"Data Source=.\SQLExpress;Integrated Security=True");
ServerConnection srvConn = new ServerConnection(Connection);
Server srvSql = new Server(srvConn);
if (srvSql != null)
{
if (openBackupDialog.ShowDialog() == DialogResult.OK)
{
Restore rstDatabase = new Restore();
rstDatabase.Action = RestoreActionType.Database;
rstDatabase.Database = "mydb2" + chngdt + "";
string dbNm = "mydb2" + chngdt + "";
string dbLogNm = "mydb2" + chngdt + "_Log";
rstDatabase.RelocateFiles.Add(new RelocateFile(dbNm, clientMDFPath));
rstDatabase.RelocateFiles.Add(new RelocateFile(dbLogNm, clientLDFPath));
BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);
rstDatabase.Devices.Add(bkpDevice);
rstDatabase.ReplaceDatabase = true;
Connection.Open();
rstDatabase.SqlRestore(srvSql);
string sqlOn = "alter database mydb2" + chngdt + " set online with rollback immediate";
SqlCommand cmdOn = new SqlCommand(sqlOn, Connection);
try
{
cmdOn.ExecuteNonQuery();
}
catch (Exception esp)
{
}
MessageBox.Show("Database restored");
}
}
else
{
MessageBox.Show("A connection to a SQL server was not established.", "Not Connected to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
It gives me an error on following code:
rstDatabase.SqlRestore(srvSql);
Error :
Restore failed for Server 'SHAILESH-LP\SQLExpress'.
InnerException :
{"Directory lookup for the file \"C:\\Demo\\mysql\\mydb1_data.mdf\" failed with the operating system error 3(The system cannot find the path specified.).\r\nFile 'test_data' cannot be restored to 'C:\\Demo\\mysql\\mydb1_data.mdf'. Use WITH MOVE to identify a valid location for the file.\r\nDirectory lookup for the file \"C:\\Demo\\mysql\\mydb1_log.ldf\" failed with the operating system error 3(The system cannot find the path specified.).\r\nFile 'mydbb_log' cannot be restored to 'C:\\Demo\\mysql\\mydb1_log.ldf'. Use WITH MOVE to identify a valid location for the file.\r\nProblems were identified while planning for the RESTORE statement. Previous messages provide details.\r\nRESTORE DATABASE is terminating abnormally."}
Can anybody help me to solve this...
Thanks in advance.
Shailesh J.
:-)