private void LoadDatabases(string service)
{
if (service == "Please select a SQL Server...")
{
return;
}
try
{
SqlConnection sqlCon = new SqlConnection(@"Data Source=STEVERODRIGUEZ\" + service + ";Initial Catalog=MASTER;User ID=sa;Password=sa;");
var sqlQuery = sqlCon.Query <string>("SELECT NAME FROM sys.databases WHERE name NOT IN ('master','tempdb','model','msdb')").AsList();
lbDatabaseList.Items.Clear();
foreach (string database in sqlQuery)
{
lbDatabaseList.Items.Add(database);
}
}
catch (Exception errorThrown)
{
string errorMessage = "There was an exception connecting to the selected SQL Service. Please ensure the selected Sql Service is still running and try again.";
ExceptionHandling.LogException(errorThrown.ToString(), errorMessage);
MessageBox.Show(errorMessage);
}
}
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);
}
