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; }