public List <string> GetDatabases()
{
List <string> databases = new List <string>();
string sqlCommand = "SELECT name FROM master.sys.databases where name NOT IN ('master','model','msdb','tempdb')";
try
{
if (connector.Open())
{
if (filterTextBox.Text != default_text)
{
sqlCommand += " AND name LIKE '%" + filterTextBox.Text + "%'";
}
var reader = connector.ReadResults(connector.CreateCommand(sqlCommand));
while (reader.Read())
{
databases.Add(reader[0].ToString());
}
reader.Close();
if (connector.GetConnectionState() == ConnectionState.Open)
{
connector.Close();
}
}
return(databases);
}
catch (Exception e)
{
Console.WriteLine($"{e}: There was an error while retrieving the database list.");
return(databases);
}
}
private void backgroundWorker3_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker worker = sender as BackgroundWorker;
for (int i = 1; i < backupList.Items.Count; i += 2)
{
if (connector2.Open())
{
try
{
string sql = $"BACKUP DATABASE \"{backupList.Items[i]}\" TO DISK = \'{backupDirectoryTextBox.Text}\\{backupList.Items[i]}.BAK\' WITH INIT";
// string sql = $"BACKUP DATABASE \"{s}\" TO DISK = \'{backupDirectoryTextBox.Text}\\{s}.BAK\'";
var command = connector2.CreateCommand(sql);
command.CommandTimeout = 0;
var reader = connector2.ReadResults(command);
if (connector2.GetConnectionState() == ConnectionState.Open)
{
connector2.Close();
}
Logger.Info($"Successfully Backed Up Database: {backupList.Items[i]}");
}
catch (Exception ex)
{
connector2.Close();
Logger.Error(ex, $"An Error Occurred While Attempting to Backup Database: {backupList.Items[i]}");
// This is extremely annoying and unreliable when errors occur on multiple databases.
//ef = new ErrorForm(ex);
//ef.Show();
break;
}
}
int percentComplete = (int)(i / (float)(backupList.Items.Count) * 100);
worker.ReportProgress(percentComplete);
}
}
private SQLConnector connectToSQL()
{
SQLConnector conn = null;
try
{
conn = new SQLConnector(Dashboard.SqlInfoData.Data_Source, Dashboard.SqlInfoData.Initial_Catalog, Dashboard.SqlInfoData.User_Id, Dashboard.SqlInfoData.Password);
conn.InitializeConnection();
}
catch (Exception ex)
{
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
Logger.Error(ex, $"An error occurred while attempting to estbalish SQL Connection. " +
$"Data Source: {Dashboard.SqlInfoData.Data_Source}, " +
$"Initial Catalog: {Dashboard.SqlInfoData.Initial_Catalog}, " +
$"User ID: {Dashboard.SqlInfoData.User_Id}, " +
$"Password: {Dashboard.SqlInfoData.Password}");
}
return(conn);
}
private void restoreDatabase(int i, string[] filesToRestore, SQLConnector conn, String workersName)
{
string databaseName = filesToRestore[i].Split('\\').Last().Split('.').First();
string restoreSql = $@"RESTORE DATABASE [{databaseName}] FROM DISK='{filesToRestore[i]}' WITH REPLACE";
Console.WriteLine($"{workersName} runs: Restoring {databaseName} using query: {restoreSql}");
Logger.Info($"{workersName} runs: Restoring {databaseName} using query: {restoreSql}");
try
{
conn.Open();
var command = conn.CreateCommand(restoreSql);
command.CommandTimeout = 0;
conn.ReadResults(command);
conn.Close();
}
catch (Exception ex)
{
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
Logger.Error(ex, $"An error occurred while attempting to restore {databaseName}");
}
}
//checks over all databases that were restored and checks there state. If any are stuck in restoring state, then restore them again (single threaded at this point)
private void stuckRestoringCheck(string[] filesToRestore, SQLConnector conn)
{
conn.Open();
var dbResults = conn.ReadResults(conn.CreateCommand("SELECT NAME FROM SYS.DATABASES WHERE NAME NOT IN ('tempdb', 'master', 'model', 'msdb')"));
List <string> databases = new List <string>();
while (dbResults.Read())
{
databases.Add(dbResults[0].ToString());
}
conn.Close();
Boolean keepgoing = true;
//make sure all other background workers are done with their restoring before proceeding
while (keepgoing)
{
int workersWorking = 0;
if (backgroundWorker1.IsBusy)
{
workersWorking++;
}
if (backgroundWorker3.IsBusy)
{
workersWorking++;
}
if (backgroundWorker4.IsBusy)
{
workersWorking++;
}
if (backgroundWorker5.IsBusy)
{
workersWorking++;
}
if (workersWorking == 1)
{
keepgoing = false;
}
}
//Console.Clear(); //this line seems to cause problems for some reason. didn't investigate, just disabled since non-essential
for (int i = 0; i < databases.Count; i++)
{
string dbName = databases[i];
string sql_check_state = $"SELECT DATABASEPROPERTYEX('{dbName}', 'Status')";
string result;
if (conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
try
{
conn.Open();
var reader = conn.ReadResults(conn.CreateCommand(sql_check_state));
reader.Read();
result = reader[0].ToString(); //read the result's first row grab its first column
Console.WriteLine(dbName + ": " + result);
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
if (conn != null && conn.GetConnectionState() == ConnectionState.Open)
{
conn.Close();
}
result = "failed";
}
if (!result.Equals("ONLINE")) //if this database's state is not ONLINE then restore it again
{
Console.WriteLine($"{dbName} currently in state: {result}");
Console.WriteLine("restoring... starting check over again");
Logger.Info($"{dbName} currently in state: {result} - restoring... starting check over again");
restoreDatabase(i, filesToRestore, conn, "Final Check");
i = -1; //reset, start check over again to check this one again
}
}
}