public async static Task<string> GenerateScriptShrink(ModelBackupSchedule _databaseSchedule) { try { string _fileNameFullScript = null; _fileNameFullScript = _databaseSchedule.Databases.Directory + @"/" + _databaseSchedule.Directory + @"/" + "Shrink" + _databaseSchedule.Databases.DatabaseName + ".sql"; string script = $"USE [{_databaseSchedule.Databases.DatabaseName}]" + "\n"; script += $"DBCC SHRINKFILE (CeltaBSYoguty_log, 0, TRUNCATEONLY);"; if (await SystemFileHelps.FileExist(_fileNameFullScript)) { return _fileNameFullScript; } else { if(await SystemFileHelps.CreateFile(_fileNameFullScript, script, false)) return _fileNameFullScript; } return _fileNameFullScript; } catch (Exception err) { return "Error: " + err.Message; } }
private async Task <string> ExecChangeRecoveyModelType(ModelBackupSchedule _backupSchedule, ModelTaskServiceSettings _setting, int _recoveryTypeModel) { UtilitariosInfra.UtilTelegram _utilTelegram = new UtilitariosInfra.UtilTelegram(_setting.UidTelegramToken); try { string url = "http://" + _backupSchedule.CustomerProduct.Server.IpAddress + ":" + _backupSchedule.CustomerProduct.Server.Port; if (_recoveryTypeModel == 1) { _backupSchedule.RecoveryTypeModel = 1; } else { _backupSchedule.RecoveryTypeModel = 3; } var json = JsonConvert.SerializeObject(_backupSchedule); var stringContent = new StringContent(json, UnicodeEncoding.UTF8, "application/json"); var streamResult = await client.PostAsync(url + "/api/DatabaseService/ChangeRecoveryModel", stringContent); if (!streamResult.IsSuccessStatusCode) { return(await streamResult.Content.ReadAsStringAsync()); } return("sucess"); } catch (Exception err) { _utilTelegram.SendMessage($"Falha alterar tipo de recuperação: {_backupSchedule.Databases.DatabaseName}:{_backupSchedule.DateHourExecution.ToShortTimeString()}\n {err.Message}", _setting.UidTelegramDestino); return("false"); } }
public async static Task<string> GenerateScriptValidate(ModelBackupSchedule _databaseSchedule, ServicesCeltaWare.Model.Enum.ValidateType _validateType) { try { string ScriptFileNameFull = ReturnScriptName(_databaseSchedule, true); string backupName = ReturnBackupName(_databaseSchedule); string commandValidate = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.CustomerProduct.LoginPassword} -i /var/opt/mssql/{_databaseSchedule.Directory}/Validate{_databaseSchedule.Databases.DatabaseName + _databaseSchedule.DateHourExecution.ToShortTimeString()}.sql"; bool res = await CreateFile(_validateType, backupName, ScriptFileNameFull, _databaseSchedule); while (!res) { res = await CreateFile(_validateType, backupName, ScriptFileNameFull, _databaseSchedule); } return commandValidate; } catch (Exception err) { return "Error: " + err.Message; } }
public async Task <IActionResult> Shrink(ModelBackupSchedule _databaseSchedule) { try { string result = await DatabaseServiceHelper.GenerateScriptShrink(_databaseSchedule); if (String.IsNullOrEmpty(result)) { return(BadRequest("Falha na criação do script Shrink")); } string command = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.CustomerProduct.LoginPassword} -i /var/opt/mssql/{_databaseSchedule.Directory}/Shrink{_databaseSchedule.Databases.DatabaseName}.sql"; string message = await DatabaseServiceHelper.Execute(command); if (message.Contains("Sqlcmd: Error:") || message.Contains("Incorrect syntax") || message.Contains("Unknown Option") || message.Contains("Erro") || message.Contains("Invalid filename")) { return(BadRequest(message + "\n" + $"DBCC SHRINKFILE ({_databaseSchedule.Databases.DatabaseName}, 0, TRUNCATEONLY);")); } else { return(Ok(message)); } } catch (Exception err) { return(BadRequest(err.Message)); } }
public static async Task<string> ChangeRecoveryModelType(ModelBackupSchedule _databaseSchedule, string recoveryModeValue) { try { string _command = null; string script = null; if (recoveryModeValue == "1") { script = $"ALTER DATABASE { _databaseSchedule.Databases.DatabaseName} SET RECOVERY FULL WITH NO_WAIT"; await SystemFileHelps.CreateFile($"{_databaseSchedule.Databases.Directory}/{_databaseSchedule.Directory}/setRecoveryFull{_databaseSchedule.Databases.DatabaseName}.sql", script, false); _command = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.Databases.CustomerProduct.LoginPassword} -i /var/opt/mssql/{_databaseSchedule.Directory}/setRecoveryFull{_databaseSchedule.Databases.DatabaseName}.sql"; } else if (recoveryModeValue == "3") { script = $"ALTER DATABASE {_databaseSchedule.Databases.DatabaseName} SET RECOVERY SIMPLE WITH NO_WAIT"; await SystemFileHelps.CreateFile($"{_databaseSchedule.Databases.Directory}/{_databaseSchedule.Directory}/setRecoverySimple{_databaseSchedule.Databases.DatabaseName}.sql", script, false); _command = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.Databases.CustomerProduct.LoginPassword} -i /var/opt/mssql/{_databaseSchedule.Directory}/setRecoverySimple{_databaseSchedule.Databases.DatabaseName}.sql"; } else return $"Opção inválida{recoveryModeValue}(DatabaseServiceHelper/ChangeRecoveryModelType). 1 para FULL e 3 para SIMPLE."; return await CommandBash.Execute(_command); } catch(Exception err) { if (!String.IsNullOrEmpty(err.InnerException.Message)) { return err.Message + "\n" + err.InnerException.Message; } return err.Message; } }
public static async Task<string> ReturnRecoveryModelType(ModelBackupSchedule _databaseSchedule) { try { string _command = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.Databases.CustomerProduct.LoginPassword} -Q \"SELECT recovery_model_desc FROM sys.databases WHERE name = '{_databaseSchedule.Databases.DatabaseName}'\"; "; string msg = await CommandBash.Execute(_command); if (msg.Contains("FULL")) return "1"; else if (msg.Contains("SIMPLE")) return "3"; else if(msg.Contains("(0 rows affected)")) { return "0 rows affected"; } else return msg; } catch (Exception err) { if (!String.IsNullOrEmpty(err.InnerException.Message)) { return err.Message + "\n" + err.InnerException.Message; } return err.Message; } }
public async Task <IActionResult> ChangeRecoveryModel(ModelBackupSchedule _databaseSchedule) { // 1 = FULL // 3 = SIMPLE try { string resp = null; switch (_databaseSchedule.RecoveryTypeModel) { case 1: { string valid = await DatabaseServiceHelper.ReturnRecoveryModelType(_databaseSchedule); if (valid.Equals("1")) { //não faça nada pois já esta como FULL!!! } else { resp = await DatabaseServiceHelper.ChangeRecoveryModelType(_databaseSchedule, "1"); } break; } case 3: { string valid = await DatabaseServiceHelper.ReturnRecoveryModelType(_databaseSchedule); if (valid.Equals("3")) { //não faça nada pois já esta como SIMPLE!!! } else { resp = await DatabaseServiceHelper.ChangeRecoveryModelType(_databaseSchedule, "3"); } break; } default: { return(BadRequest($"Opção inválida:{_databaseSchedule.RecoveryTypeModel.ToString()}. 1 para FULL ou 3 para SIMPLE.")); } } if (!String.IsNullOrEmpty(resp)) { return(BadRequest(resp)); } return(Ok()); } catch (Exception err) { return(BadRequest(err.Message)); } }
private static string ReturnScriptName(ModelBackupSchedule _databaseSchedule, bool isValidType) { string fileNameFullScript = null; if (isValidType) { fileNameFullScript = _databaseSchedule.Databases.Directory + @"/" + _databaseSchedule.Directory + @"/Validate" + _databaseSchedule.Databases.DatabaseName + _databaseSchedule.DateHourExecution.ToShortTimeString() + ".sql"; } return fileNameFullScript; }
public async static Task<string> GenerateScriptBackup(ModelBackupSchedule _databaseSchedule) { try { string fileNameFullScript = _databaseSchedule.Databases.Directory + @"/" +_databaseSchedule.Directory +@"/" + _databaseSchedule.Databases.DatabaseName + _databaseSchedule.DateHourExecution.ToShortTimeString() + ".sql"; string backupName; if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Full) backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupFull.bak"; else if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Diferential) backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupDiff{_databaseSchedule.DateHourExecution.Hour.ToString() + _databaseSchedule.DateHourExecution.Minute.ToString()}.bak"; else backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupIncremetalLog{_databaseSchedule.DateHourExecution.Hour.ToString() + _databaseSchedule.DateHourExecution.Minute.ToString()}.bak"; string commandBackup = $"docker exec -i {_databaseSchedule.Databases.ConteinerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P {_databaseSchedule.CustomerProduct.LoginPassword} -i /var/opt/mssql/{_databaseSchedule.Directory}/{_databaseSchedule.Databases.DatabaseName + _databaseSchedule.DateHourExecution.ToShortTimeString()}.sql"; string scriptBackup; if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Full) { scriptBackup = $"BACKUP DATABASE [{_databaseSchedule.Databases.DatabaseName}] TO DISK = N\'/var/opt/mssql/{_databaseSchedule.Directory}/{backupName}\'"; scriptBackup += $" WITH NOFORMAT, INIT, NAME = N'{backupName}-Banco de Dados Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"; } else if(_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Diferential) { scriptBackup = $"BACKUP DATABASE [{_databaseSchedule.Databases.DatabaseName}] TO DISK = N\'/var/opt/mssql/{_databaseSchedule.Directory}/{backupName}\'"; scriptBackup += $" WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = N'{backupName}-Banco de Dados Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"; } else { scriptBackup = $"BACKUP LOG [{_databaseSchedule.Databases.DatabaseName}] TO DISK = N\'/var/opt/mssql/{_databaseSchedule.Directory}/{backupName}\'"; scriptBackup += $" WITH FORMAT, INIT, NAME = N'{backupName}-Banco de Dados Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10"; } if (await SystemFileHelps.FileExist(fileNameFullScript)) { return commandBackup; } else { await SystemFileHelps.CreateFile(fileNameFullScript, scriptBackup, false); return "back"; } } catch(Exception err) { return "Error" + err.Message; } }
public ActionResult Add([FromBody] ModelBackupSchedule backupSchedule) { try { _repository.Add(backupSchedule); return(Ok()); } catch (Exception err) { return(NotFound(err.Message)); } }
public static string ReturnBackupName(ModelBackupSchedule _databaseSchedule) { string backupName; if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Full) backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupFull.bak"; else if(_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.Incremental) backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupIncremetalLog{_databaseSchedule.DateHourExecution.Hour.ToString() + _databaseSchedule.DateHourExecution.Minute.ToString()}.bak"; else { backupName = $"{_databaseSchedule.Databases.DatabaseName}BackupDiff{_databaseSchedule.DateHourExecution.Hour.ToString() + _databaseSchedule.DateHourExecution.Minute.ToString()}.bak"; } return backupName; }
public IActionResult UpdateDateHourLastExecution(ModelBackupSchedule _databaseSchedule) { try { var bkpSchedule = _repository.Find(_databaseSchedule.BackupScheduleId); bkpSchedule.DateHourLastExecution = DateTime.Now; _repository.Update(bkpSchedule); return(Ok()); } catch (Exception err) { return(BadRequest(err.Message)); } }
public IActionResult UpdateStatus(ModelBackupSchedule _databaseSchedule) { try { var bkpSchedule = _repository.Find(_databaseSchedule.BackupScheduleId); bkpSchedule.BackupStatus = _databaseSchedule.BackupStatus; bkpSchedule.GoogleDriveFileId = _databaseSchedule.GoogleDriveFileId; bkpSchedule.DateHourLastExecution = DateTime.Now; bkpSchedule.BackupExecDateHourStart = _databaseSchedule.BackupExecDateHourStart; bkpSchedule.BackupExecDateHourFinish = _databaseSchedule.BackupExecDateHourFinish; bkpSchedule.BackupExecTotalTime = 0; bkpSchedule.UploadDateHourStart = _databaseSchedule.UploadDateHourStart; bkpSchedule.UploadDateHourFinish = _databaseSchedule.UploadDateHourFinish; bkpSchedule.UploadTotalTime = 0; _repository.Update(bkpSchedule); return(Ok()); } catch (Exception err) { return(BadRequest(err.Message + "\n" + err.InnerException.Message)); } }
public async Task <IActionResult> BackupExec(ModelBackupSchedule _databaseSchedule) { try { string message = null; string scriptBackup = null; if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.MysqlFull) { scriptBackup = $"docker exec {_databaseSchedule.Databases.ConteinerName} /usr/bin/mysqldump -u root --password={_databaseSchedule.Databases.DatabaseUserSa.Password} celtabspdvconcentrador > {_databaseSchedule.Databases.Directory}/{_databaseSchedule.Directory}/{_databaseSchedule.Databases.ConteinerName}Backup.sql"; } else { scriptBackup = await DatabaseServiceHelper.GenerateScriptBackup(_databaseSchedule); if (scriptBackup.Contains("back")) { scriptBackup = await DatabaseServiceHelper.GenerateScriptBackup(_databaseSchedule); } } message = await DatabaseServiceHelper.Execute(scriptBackup); if (message.Contains("Sqlcmd: Error:") || message.Contains("Incorrect syntax") || message.Contains("Unknown Option") || message.Contains("Erro") || message.Contains("Invalid filename") || message.Contains("error") || message.Contains("error: 1045") && !message.Contains("BACKUP DATABASE successfully")) { return(BadRequest(message)); } else { return(Ok(message)); } } catch (Exception err) { return(BadRequest(err.Message)); } }
public async Task <IActionResult> ValidateBackupExec(ModelBackupSchedule _databaseSchedule) { try { if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.MysqlFull) { return(Ok()); } string scriptValidate = null; scriptValidate = await DatabaseServiceHelper.GenerateScriptValidate(_databaseSchedule, ServicesCeltaWare.Model.Enum.ValidateType.LabelOnly); string message = await DatabaseServiceHelper.Execute(scriptValidate); if (message.Contains("Sqlcmd: Error:") || message.Contains("Incorrect syntax") || message.Contains("Unknown Option") || message.Contains("Erro") || message.Contains("Invalid filename") || message.Contains("is terminating abnormally")) { return(BadRequest(message + scriptValidate)); } scriptValidate = await DatabaseServiceHelper.GenerateScriptValidate(_databaseSchedule, ServicesCeltaWare.Model.Enum.ValidateType.VerifyOnly); message += await DatabaseServiceHelper.Execute(scriptValidate); if (message.Contains("Sqlcmd: Error:") || message.Contains("Incorrect syntax") || message.Contains("Unknown Option") || message.Contains("Erro") || message.Contains("is terminating abnormally") /* && !message.Contains("BACKUP DATABASE successfully")*/) { return(BadRequest(message + scriptValidate)); } return(Ok(message)); } catch (Exception err) { return(BadRequest(err.Message)); } }
public async Task <IActionResult> Upload(ModelBackupSchedule _databaseSchedule) { try { ModelGoogleDrive googleDrive = new ModelGoogleDrive(); googleDrive = Helpers.HelperGoogleDrive.LoadSetting(_configuration); string path = _databaseSchedule.Databases.Directory + "/" + _databaseSchedule.Directory + "/"; string backupFileName = null; if (_databaseSchedule.Type == ServicesCeltaWare.Model.Enum.BackuypType.MysqlFull) { backupFileName = $"{_databaseSchedule.Databases.ConteinerName}Backup.sql"; } else { backupFileName = Helpers.DatabaseServiceHelper.ReturnBackupName(_databaseSchedule); } if (_databaseSchedule.GoogleDriveFolderId == null) { return(BadRequest($"{_databaseSchedule.Databases.DatabaseName} GoogleDriveFolderId nulo!")); } var resp = await Helpers.HelperGoogleDrive.UploadFromLinux(googleDrive.CredentialFileName, backupFileName, path, _databaseSchedule.GoogleDriveFolderId); if (resp.Contains("The access token has expired and could not be refreshed")) { HelperLogs.WriteLog("GoogleDriveServiceController. ", resp); var respUpdate = await Helpers.HelperGoogleDrive.UpdateCredential(googleDrive.CredentialFileName); if (respUpdate.ToUpperInvariant().Contains("OK")) { var resp2 = await Helpers.HelperGoogleDrive.UploadFromLinux(googleDrive.CredentialFileName, backupFileName, path, _databaseSchedule.GoogleDriveFolderId); } else { return(BadRequest("Não foi possível atualizar arquivo de credencial. \n" + respUpdate)); } } if (resp.Contains("Failed")) { HelperLogs.WriteLog("GoogleDriveServiceController", resp); await Task.Delay(2000); var resp2 = await Helpers.HelperGoogleDrive.UploadFromLinux(googleDrive.CredentialFileName, backupFileName, path, _databaseSchedule.GoogleDriveFolderId); if (resp2.Contains("ok")) { resp = resp2; } else { HelperLogs.WriteLog("GoogleDriveServiceController", resp2); return(BadRequest("Não foi possível atualizar o backup pela segunda vez!. \n" + resp2)); } } if (resp.Contains("ERRO: Arquivo não existe") || resp.Contains("Error:\"invalid_grant\"") || resp.Contains("The operation was canceled")) { HelperLogs.WriteLog("GoogleDriveServiceController", resp); return(Forbid()); } return(Ok(resp)); } catch (Exception err) { if (err.InnerException != null) { HelperLogs.WriteLog("GoogleDriveServiceController", err.Message + "\n" + err.InnerException.Message); return(BadRequest(err.Message + "\n" + err.InnerException.Message)); } HelperLogs.WriteLog("GoogleDriveServiceController", err.Message); return(BadRequest(err.Message)); } }
private static async Task<bool> CreateFile(ServicesCeltaWare.Model.Enum.ValidateType _validateType, string backupName, string _fileNameFullScript, ModelBackupSchedule _backupSchedule) { string scriptBackup = null; if (_validateType == ServicesCeltaWare.Model.Enum.ValidateType.LabelOnly) { scriptBackup = $"Restore LabelOnly from Disk = \'/var/opt/mssql/{_backupSchedule.Directory}/{backupName}\'"; } else if (_validateType == ServicesCeltaWare.Model.Enum.ValidateType.VerifyOnly) { scriptBackup = $"Restore VerifyOnly from Disk = \'/var/opt/mssql/{_backupSchedule.Directory}/{backupName}\'"; } if (await SystemFileHelps.FileExist(_fileNameFullScript)) { return true; } else { await SystemFileHelps.CreateFile(_fileNameFullScript, scriptBackup, false); return false; } }