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