private void btnRun_Click(object sender, EventArgs e)
{
if (lbDatabaseList.SelectedItems.Count == 0)
{
MessageBox.Show("Please select a database to reset the version of.");
return;
}
StringBuilder builder = new StringBuilder();
foreach (string database in lbDatabaseList.SelectedItems)
{
builder.Append(database.ToString()).AppendLine();
}
string message = String.Format("Are you sure you want to reset the database version for the selected database(s)?\n\n{0}", builder.ToString());
string caption = "CONFIRM";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
MessageBoxIcon icon = MessageBoxIcon.Question;
DialogResult result;
result = MessageBox.Show(message, caption, buttons, icon);
if (result == DialogResult.Yes)
{
List <string> runningSQLServer = SQLManagement.GetRunningSQLServers();
if (runningSQLServer.Count > 1)
{
MessageBox.Show("There are multiple sql servers running. Please stop any sql servers not being used. Environment Manager will target the remaining running sql server.");
return;
}
if (runningSQLServer.Count == 0)
{
MessageBox.Show("There are no sql servers running. Please start a sql server and try again.");
return;
}
foreach (string server in runningSQLServer)
{
foreach (string database in lbDatabaseList.SelectedItems)
{
string script = String.Format(@"USE {0} EXEC dbo.sppResetDatabase", database);
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + Environment.MachineName + "\\" + server + @";Initial Catalog=MASTER;User ID=sa;Password=sa;");
SqlDataAdapter sqlAdapter = new SqlDataAdapter(script, sqlCon);
DataTable dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
}
}
this.Close();
}
return;
}
private void ResetDBVersion_Load(object sender, EventArgs e)
{
try
{
List <string> runningSQLServer = SQLManagement.GetRunningSQLServers();
if (runningSQLServer.Count > 1)
{
MessageBox.Show("There are multiple sql servers running. Please stop any sql servers not being used. Environment Manager will target the remaining running sql server.");
return;
}
if (runningSQLServer.Count == 0)
{
MessageBox.Show("There are no sql servers running. Please start a sql server and try again.");
return;
}
foreach (string server in runningSQLServer)
{
try
{
SqlConnection sqlCon = new SqlConnection(String.Format(@"Data Source={0}\{1};Initial Catalog=MASTER;User ID=sa;Password=sa;", Environment.MachineName, server));
var sqlQuery = sqlCon.Query <string>(sqlDatabaseQuery).AsList();
lbDatabaseList.Items.Clear();
foreach (string database in sqlQuery)
{
lbDatabaseList.Items.Add(database);
}
}
catch (Exception sqlError)
{
string errorMessage = "There was an error retrieving the existing databases.";
ExceptionHandling.LogException(sqlError.ToString(), errorMessage);
MessageBox.Show(errorMessage);
}
}
}
catch (Exception queryError)
{
MessageBox.Show(String.Format("There was an error\n\n{0}", queryError.ToString()));
return;
}
}
public void NewBackupThread(string dynamicsScript, string nonMBScript, string mbScript, string dbPath, string bakName, string bakDescription)
{
_form1.DisableDBControls(false);
List <string> runningSQLServer = SQLManagement.GetRunningSQLServers();
if (runningSQLServer.Count > 1)
{
MessageBox.Show("There are multiple sql servers running. Please stop any sql servers not being used. Environment Manager will target the remaining running sql server.");
return;
}
if (runningSQLServer.Count == 0)
{
MessageBox.Show("There are no sql servers running. Please start a sql server and try again.");
return;
}
foreach (string server in runningSQLServer)
{
try
{
Directory.Delete(dbPath, true);
}
catch (Exception e1)
{
MessageBox.Show("Failed deleting the selected db backup " + dbPath + "\n\n" + e1);
return;
}
try
{
Directory.CreateDirectory(dbPath);
}
catch (Exception e2)
{
MessageBox.Show("Failed creating the following directory " + dbPath + "\n\n" + e2);
return;
}
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + Environment.MachineName + "\\" + server + @";Initial Catalog=MASTER;User ID=sa;Password=sa;");
SqlDataAdapter restoreDynScript = new SqlDataAdapter(dynamicsScript, sqlCon);
DataTable restoreDynTable = new DataTable();
restoreDynScript.Fill(restoreDynTable);
SqlDataAdapter restoreNonMBScript = new SqlDataAdapter(nonMBScript, sqlCon);
DataTable restoreNonMBTable = new DataTable();
restoreNonMBScript.Fill(restoreNonMBTable);
SqlDataAdapter restoreMBScript = new SqlDataAdapter(mbScript, sqlCon);
DataTable restoreMBTable = new DataTable();
restoreMBScript.Fill(restoreMBTable);
}
//catch (SqlException e)
//{
// Directory.Delete(dbPath, true);
// stopProcess = true;
// string errorMessage = "There was an error creating a new Database Backup. \n\nWould you like to view the exception?";
// string errorCaption = "ERROR";
// MessageBoxButtons errorButton = MessageBoxButtons.YesNo;
// MessageBoxIcon errorIcon = MessageBoxIcon.Error;
// DialogResult errorResult;
// errorResult = MessageBox.Show(errorMessage, errorCaption, errorButton, errorIcon);
// this.Close();
// if (errorResult == DialogResult.Yes)
// {
// _form1.DisableDBControls(true);
// MessageBox.Show(Convert.ToString(e));
// }
// if (errorResult == DialogResult.No)
// {
// _form1.DisableDBControls(true);
// }
// return;
//}
using (StreamWriter sw = File.AppendText(dbPath + @"\Description.txt"))
{
sw.WriteLine("===============================================================================");
sw.WriteLine("BACKUP - " + bakName);
sw.WriteLine(DateTime.Now);
sw.WriteLine(bakDescription);
}
// WRITE DATABASE ACTIVITY TO DatabaseActivityLog TABLE
DatabaseActivityLogModel databaseActivity = new DatabaseActivityLogModel(Convert.ToString(DateTime.Now), "CREATED", bakName);
SqliteDataAccess.WriteDatabaseActivity(databaseActivity);
ZipFile.CreateFromDirectory(dbPath, dbPath + ".zip");
Directory.Delete(dbPath, true);
string newMessage = "Backup \"" + bakName + "\" was created successfully.";
string newCaption = "COMPLETE";
MessageBoxButtons newButtons = MessageBoxButtons.OK;
MessageBoxIcon newIcon = MessageBoxIcon.Exclamation;
DialogResult newResult;
newResult = MessageBox.Show(newMessage, newCaption, newButtons, newIcon);
_form1.DisableDBControls(true);
}
public void OverwriteDBThread(string dynamicsScript, string nonMBScript, string mbScript, string dbPath)
{
try
{
_form1.DisableDBControls(false);
string bakDescription = tbDBBackupDescription.Text;
List <string> runningSQLServer = SQLManagement.GetRunningSQLServers();
if (runningSQLServer.Count > 1)
{
MessageBox.Show("There are multiple sql servers running. Please stop any sql servers not being used. Environment Manager will target the remaining running sql server.");
return;
}
if (runningSQLServer.Count == 0)
{
MessageBox.Show("There are no sql servers running. Please start a sql server and try again.");
return;
}
foreach (string server in runningSQLServer)
{
try
{
//Directory.Delete(dbPath, true);
File.Delete(dbPath + ".zip");
}
catch (Exception e1)
{
MessageBox.Show("Failed deleting the selected db backup " + dbPath + "\n\n" + e1);
return;
}
try
{
Directory.CreateDirectory(dbPath);
}
catch (Exception e2)
{
MessageBox.Show("Failed creating the following directory " + dbPath + "\n\n" + e2);
return;
}
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + Environment.MachineName + "\\" + server + @";Initial Catalog=MASTER;User ID=sa;Password=sa;");
SqlDataAdapter restoreDynScript = new SqlDataAdapter(dynamicsScript, sqlCon);
DataTable restoreDynTable = new DataTable();
restoreDynScript.Fill(restoreDynTable);
SqlDataAdapter restoreNonMBScript = new SqlDataAdapter(nonMBScript, sqlCon);
DataTable restoreNonMBTable = new DataTable();
restoreNonMBScript.Fill(restoreNonMBTable);
SqlDataAdapter restoreMBScript = new SqlDataAdapter(mbScript, sqlCon);
DataTable restoreMBTable = new DataTable();
restoreMBScript.Fill(restoreMBTable);
}
using (StreamWriter sw = File.AppendText(dbPath + @"\Description.txt"))
{
sw.WriteLine("===============================================================================");
sw.WriteLine("BACKUP - " + dbToCreate);
sw.WriteLine(DateTime.Now);
sw.WriteLine(bakDescription);
}
// WRITE DATABASE ACTIVITY TO DatabaseActivityLog TABLE
DatabaseActivityLogModel databaseActivity = new DatabaseActivityLogModel(Convert.ToString(DateTime.Now), "OVERWRITE", dbToCreate);
SqliteDataAccess.WriteDatabaseActivity(databaseActivity);
ZipFile.CreateFromDirectory(dbPath, dbPath + ".zip");
string message = "Backup \"" + dbToCreate + "\" was overwritten successfully.";
string caption = "COMPLETE";
MessageBoxButtons button = MessageBoxButtons.OK;
MessageBoxIcon icon = MessageBoxIcon.Exclamation;
DialogResult Result;
Result = MessageBox.Show(message, caption, button, icon);
_form1.DisableDBControls(true);
this.Close();
return;
}
catch (Exception x1)
{
MessageBox.Show("There was an exception preforming the backup SQL \n\n" + x1);
return;
}
}
public void RestoreDatabase(string[] selectedFiles, string filePath, string backupName)
{
_form1.DisableDBControls(false);
_form1.DisableSQLControls(false);
List <string> runningSQLServer = SQLManagement.GetRunningSQLServers();
if (runningSQLServer.Count > 1)
{
MessageBox.Show("There are multiple sql servers running. Please stop any sql servers not being used. Environment Manager will target the remaining running sql server.");
_form1.DisableDBControls(true);
_form1.DisableSQLControls(true);
return;
}
if (runningSQLServer.Count == 0)
{
MessageBox.Show("There are no sql servers running. Please start a sql server and try again.");
_form1.DisableDBControls(true);
_form1.DisableSQLControls(true);
return;
}
//Unzip database
ZipFile.ExtractToDirectory(filePath + ".zip", filePath);
foreach (string server in runningSQLServer)
{
try
{
SqlConnection sqlCon = new SqlConnection(@"Data Source=" + Environment.MachineName + "\\" + server + @";Initial Catalog=MASTER;User ID=sa;Password=sa;");
foreach (string file in selectedFiles)
{
string restoreScript = @"ALTER DATABASE " + file + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE; RESTORE DATABASE " + file + " FROM DISK='" + filePath + "\\" + file + ".bak' WITH FILE = 1, NOUNLOAD, REPLACE; ALTER DATABASE " + file + " SET MULTI_USER;";
try
{
SqlDataAdapter restoreDynScript = new SqlDataAdapter(restoreScript, sqlCon);
DataTable restoreDynTable = new DataTable();
restoreDynScript.Fill(restoreDynTable);
}
catch (Exception restoreError)
{
string errorMessage = "There was an error restoring \"" + file + "\".";
try
{
ExceptionHandling.LogException2("Restore Database", "System.Data.SqlClient.SqlException", restoreError.Message, restoreError.Source, restoreError.StackTrace);
}
catch (Exception e1)
{
MessageBox.Show(e1.ToString());
}
MessageBox.Show(errorMessage);
_form1.DisableDBControls(true);
_form1.DisableSQLControls(true);
return;
}
}
}
catch (Exception sqlConnectionError)
{
string errorMessage = "Could not connect to the SQL Server. Please verify your SQL Server is running and try again.";
try
{
ExceptionHandling.LogException2("Restore Database", "System.Data.SqlClient.SqlException", sqlConnectionError.Message, sqlConnectionError.Source, sqlConnectionError.StackTrace);
}
catch (Exception e2)
{
MessageBox.Show(e2.ToString());
}
MessageBox.Show(errorMessage);
_form1.DisableDBControls(true);
_form1.DisableSQLControls(true);
return;
}
}
//Delete the backup folder.
Directory.Delete(filePath);
using (StreamWriter sw = File.AppendText(Environment.CurrentDirectory + @"\Files\Database Log.txt"))
{
sw.WriteLine("{" + DateTime.Now + "} - RESTORED: " + backupName);
}
string message = "Backup \"" + backupName + "\" was successfully restored.";
string caption = "COMPLETE";
MessageBoxButtons buttons = MessageBoxButtons.OK;
MessageBoxIcon icon = MessageBoxIcon.Exclamation;
DialogResult result;
result = MessageBox.Show(message, caption, buttons, icon);
_form1.DisableDBControls(true);
_form1.DisableSQLControls(true);
}