public void RestoreDb(string dbName, string resotrFilePath /*, string dtabase_MDF_Location*/)
{
killOpenProcesses(dbName);
Restore dbRestore = new Restore();
this.Cursor = Cursors.WaitCursor;
dbRestore.Database = dbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
try
{
BackupDeviceItem device = new BackupDeviceItem(resotrFilePath, DeviceType.File);
dbRestore.Devices.Add(device);
DataTable dtFiles = dbRestore.ReadFileList(sqlServer);
string backupDbLogicalName = dtFiles.Rows[0]["LogicalName"].ToString();
//RelocateFile dbRf = new RelocateFile(backupDbLogicalName, string.Format("{0}\\{1}.mdf", dtabase_MDF_Location, dbName));
//RelocateFile logRf = new RelocateFile(string.Format("{0}_log", backupDbLogicalName), string.Format("{0}\\{1}_Log.ldf", dtabase_MDF_Location, dbName));
//dbRestore.RelocateFiles.Add(dbRf);
//dbRestore.RelocateFiles.Add(logRf);
string sql = string.Empty;
StringCollection scriptColl = dbRestore.Script(sqlServer);
foreach (string str in scriptColl)
{
sql += str;
}
progBar.Visible = true;
progBar.Value = 0;
dbRestore.Complete += new ServerMessageEventHandler(dbRestore_Complete);
dbRestore.PercentComplete += new PercentCompleteEventHandler(PercentComplete);
dbRestore.SqlRestore(sqlServer);
}
catch /*(Exception ex)*/
{
dbRestore.Abort();
//Log.AddLog(ex.ToString());
}
finally
{
sqlConn.Close();
this.Cursor = Cursors.Default;
}
progBar.Visible = false;
}
public void Restore(string _BackupFileName)
{
RestartTheService();
Restore dbRestore = new Restore();
ServerConnection sc = new ServerConnection();
dbRestore.Database = _DbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
BackupDeviceItem device = new BackupDeviceItem
(_BackupFileName, DeviceType.File);
dbRestore.PercentComplete +=
new PercentCompleteEventHandler(CompletionStatusInPercent);
sc.ServerInstance = ".";
Server MyServer = new Server(sc);
try
{
dbRestore.Devices.Add(device);
RelocateFile DataFile = new RelocateFile();
//string MDF = dbRestore.ReadFileList(MyServer).Rows[0][1].ToString();
DataFile.LogicalFileName = dbRestore.ReadFileList(MyServer).Rows[0][0].ToString();
DataFile.PhysicalFileName = MyServer.Databases[_DbName].FileGroups[0].Files[0].FileName;
RelocateFile LogFile = new RelocateFile();
//string LDF = dbRestore.ReadFileList(MyServer).Rows[1][1].ToString();
LogFile.LogicalFileName = dbRestore.ReadFileList(MyServer).Rows[1][0].ToString();
LogFile.PhysicalFileName = MyServer.Databases[_DbName].LogFiles[0].FileName;
dbRestore.RelocateFiles.Add(DataFile);
dbRestore.RelocateFiles.Add(LogFile);
dbRestore.SqlRestore(MyServer);
sc.Disconnect();
}
catch (Exception exc)
{
dbRestore.Abort();
throw exc;
}
}
/// <summary>
/// Used to restore databases older than 6.0
/// </summary>
/// <param name="fileContent"></param>
/// <param name="serverAppFolder"></param>
/// <returns></returns>
//public static string RestoreDatabaseOld(string fileContent, string serverAppFolder)
//{
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Start restore"));
// string fileName = serverAppFolder + "\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".bak.gz";
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restore temp file: " + fileName));
// File.WriteAllBytes(fileName, Convert.FromBase64String(fileContent));
// // Get the stream of the source file.
// FileInfo fi = new FileInfo(fileName);
// using (FileStream inFile = fi.OpenRead())
// {
// // Get original file extension, for example "doc" from report.doc.gz.
// string curFile = fi.FullName;
// string origName = curFile.Remove(curFile.Length - fi.Extension.Length);
// //Create the decompressed file.
// using (FileStream outFile = File.Create(origName))
// {
// using (GZipStream decompress = new GZipStream(inFile,
// CompressionMode.Decompress))
// {
// //Copy the decompression stream into the output file.
// byte[] buffer = new byte[4096];
// int numRead;
// while ((numRead = decompress.Read(buffer, 0, buffer.Length)) != 0)
// {
// outFile.Write(buffer, 0, numRead);
// }
// }
// }
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Decompress finished."));
// Restore sqlRestore = new Restore();
// BackupDeviceItem deviceItem = new BackupDeviceItem(origName, DeviceType.File);
// SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
// conn.Open();
// string databaseName = conn.Database;
// ServerConnection connection = new ServerConnection(conn);
// Server sqlServer = new Server(connection);
// sqlRestore.Devices.Add(deviceItem);
// sqlRestore.Database = conn.Database;
// DataTable dt = sqlRestore.ReadBackupHeader(sqlServer);
// //Check db is for the same app version
// //Check db is for the same app version
// if (dt.Rows[0]["BackupDescription"] == DBNull.Value ||
// ((string)dt.Rows[0]["BackupDescription"] != AppVersion.Version.ToString(2) &&
// //this awful hardcoded line is to allow backups from Version 5.5.6 in version 6
// !((string)dt.Rows[0]["BackupDescription"] == "5.5" && AppVersion.Version.Major == 6)))
// {
// //invalid db
// sqlRestore.Abort();
// string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
// SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
// alter2Cmd.ExecuteNonQuery();
// return string.Format("Backups from {0} version are not supported.", dt.Rows[0]["BackupDescription"] == System.DBNull.Value ? "an older" : (string)dt.Rows[0]["BackupDescription"]);
// }
// const string UseMaster = "USE master";
// SqlCommand useMasterCommand = new SqlCommand(UseMaster, conn);
// useMasterCommand.ExecuteNonQuery();
// string alter1 = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
// SqlCommand alter1Cmd = new SqlCommand(alter1, conn);
// alter1Cmd.ExecuteNonQuery();
// Database db = sqlServer.Databases[databaseName];
// sqlRestore.Action = RestoreActionType.Database;
// String dataFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + ".mdf";
// String logFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + "_Log.ldf";
// sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA", dataFileLocation));
// sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA" + "_log", logFileLocation));
// sqlRestore.ReplaceDatabase = true;
// sqlRestore.PercentCompleteNotification = 10;
// try
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
// sqlRestore.SqlRestore(sqlServer);
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Finished restoring"));
// }
// catch (Exception ex)
// {
// return ex.Message;
// }
// finally
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing SET Multi_User"));
// string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
// SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
// alter2Cmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "SET Multi_User executed"));
// }
// db = sqlServer.Databases[databaseName];
// db.SetOnline();
// sqlServer.Refresh();
// try
// {
// if (conn.State != ConnectionState.Closed)
// {
// conn.Close();
// }
// }
// catch (Exception)
// {
// }
// try
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing test SQL"));
// conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
// const string SQLQueryTest = @"SELECT * FROM FACTOR";
// SqlCommand sqlQueryTestCmd = new SqlCommand(SQLQueryTest, conn);
// conn.Open();
// sqlQueryTestCmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Test SQL executed."));
// const string SQLAutoCloseOff = @"ALTER DATABASE idea SET AUTO_CLOSE OFF";
// sqlQueryTestCmd = new SqlCommand(SQLAutoCloseOff, conn);
// sqlQueryTestCmd.ExecuteNonQuery();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Auto_Close OFF executed."));
// }
// catch (Exception ex)
// {
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Failed executing test SQL"));
// }
// finally
// {
// if (conn.State != ConnectionState.Closed)
// {
// conn.Close();
// Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Connection closed"));
// }
// }
// return string.Empty;
// }
//}
/// <summary>
/// Restore the Data Base of Idea
/// </summary>
/// <param name="databaseBackupFileName"></param>
/// <returns></returns>
private static string RestoreDatabase(string databaseBackupFileName)
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Start restore"));
Restore sqlRestore = new Restore();
// Restore - SqlDataBase;
BackupDeviceItem deviceItem = new BackupDeviceItem(databaseBackupFileName, DeviceType.File);
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
conn.Open();
string databaseName = conn.Database;
ServerConnection connection = new ServerConnection(conn);
Server sqlServer = new Server(connection);
sqlRestore.Devices.Add(deviceItem);
sqlRestore.Database = conn.Database;
DataTable dt = sqlRestore.ReadBackupHeader(sqlServer);
//Check db is for the same app version
if (dt.Rows[0]["BackupDescription"] == System.DBNull.Value || (dt.Rows[0]["BackupDescription"] != System.DBNull.Value && (string)dt.Rows[0]["BackupDescription"] != AppVersion.Version.ToString()))
{
//invalid db
sqlRestore.Abort();
string alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
SqlCommand alter2Cmd = new SqlCommand(alter2, conn);
alter2Cmd.ExecuteNonQuery();
return(string.Format("invaliddbversion"));
}
const string useMaster = "USE master";
SqlCommand useMasterCommand = new SqlCommand(useMaster, conn);
useMasterCommand.ExecuteNonQuery();
string alter1 = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
SqlCommand alter1Cmd = new SqlCommand(alter1, conn);
alter1Cmd.ExecuteNonQuery();
Database db = sqlServer.Databases[databaseName];
sqlRestore.Action = RestoreActionType.Database;
String dataFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + ".mdf";
String logFileLocation = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DATABASE\\" + databaseName + "_Log.ldf";
Trace.WriteLine(string.Format("IDEA.ERMT: dataFileLocation: {0}", dataFileLocation));
Trace.WriteLine(string.Format("IDEA.ERMT: logFileLocation: {0}", logFileLocation));
//sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA", dataFileLocation));
//sqlRestore.RelocateFiles.Add(new RelocateFile("IDEA_log", logFileLocation));
sqlRestore.ReplaceDatabase = true;
sqlRestore.PercentCompleteNotification = 10;
try
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
sqlRestore.SqlRestore(sqlServer);
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Restoring"));
}
catch (Exception ex)
{
Trace.WriteLine(string.Format("IDEA.ERMT: Exception restoring: {0}", ex.Message));
return("errorrestoringdb");
}
finally
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing SET Multi_User"));
string alter3 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
SqlCommand alter3Cmd = new SqlCommand(alter3, conn);
alter3Cmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "SET Multi_User executed"));
}
db = sqlServer.Databases[databaseName];
db.SetOnline();
sqlServer.Refresh();
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (Exception)
{
}
try
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Executing test SQL"));
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Idea"].ConnectionString);
const string SQLQueryTest = @"SELECT * FROM FACTOR";
SqlCommand sqlQueryTestCmd = new SqlCommand(SQLQueryTest, conn);
conn.Open();
sqlQueryTestCmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Test SQL executed."));
const string SQLAutoCloseOff = @"ALTER DATABASE idea SET AUTO_CLOSE OFF";
sqlQueryTestCmd = new SqlCommand(SQLAutoCloseOff, conn);
sqlQueryTestCmd.ExecuteNonQuery();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Auto_Close OFF executed."));
}
catch (Exception ex)
{
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Failed executing test SQL"));
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
Trace.WriteLine(string.Format("IDEA.ERMT: {0}", "Connection closed"));
}
}
return(string.Empty);
}
private void RestoreDb()
{
string[] dbNameFragments = _backupFileName.Split('_');
string _dbName = dbNameFragments[0].Replace(_scratchPad, "");
Restore dbRestore = new Restore();
dbRestore.Database = _dbName;
dbRestore.Action = RestoreActionType.Database;
dbRestore.ReplaceDatabase = true;
try
{
BackupDeviceItem device = new BackupDeviceItem(_backupFileName, DeviceType.File);
dbRestore.Devices.Add(device);
DataTable dtFiles = dbRestore.ReadFileList(_sqlServer);
string backupDbLogicalName = dtFiles.Rows[0]["LogicalName"].ToString();
RelocateFile dbRf = new RelocateFile(backupDbLogicalName, _databaseFileName);
RelocateFile logRf = new RelocateFile($"{backupDbLogicalName}_log", _databaseLogFileName);
dbRestore.RelocateFiles.Add(dbRf);
dbRestore.RelocateFiles.Add(logRf);
if (!logRf.PhysicalFileName.Contains(@"C:\"))
{
logRf.PhysicalFileName = _DbFileLocation + _databaseLogFileName;
}
Logger.LogMessage("Physical Log File: " + logRf.PhysicalFileName);
Logger.LogMessage("Physical DB File: " + dbRf.PhysicalFileName);
string sql = string.Empty;
StringCollection scriptColl = dbRestore.Script(_sqlServer);
foreach (string str in scriptColl)
{
sql += str;
}
sql = "USE master ALTER DATABASE " + _dbName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE " + sql;
sql += " ALTER DATABASE " + _dbName + " SET MULTI_USER ";
txtRestoreScript.Text = sql;
Logger.LogMessage("SQL Restore Script: " + sql);
progBar.Visible = true;
progBar.Value = 0;
dbRestore.Complete += new ServerMessageEventHandler(dbRestore_Complete);
dbRestore.PercentComplete += new PercentCompleteEventHandler(PercentComplete);
dbRestore.SqlRestore(_sqlServer);
}
catch (Exception exc)
{
dbRestore.Abort();
Logger.LogMessage($"RestoreDb(): Exception occured.\nMessage: {exc.Message}");
MessageBox.Show("RestoreDb(): Exception occured.\nMessage:" + exc.Message, _messageBoxCaption);
;
}
finally
{
if (File.Exists(_backupFileName))
{
File.Delete(_backupFileName);
}
}
progBar.Visible = false;
}
