public static void RestoreDB(string backupFilePath, IConfiguration configuration, Guid requestId, ILogger<DatabaseUpgrater> logger)
{
logger.LogInformation("Restore database operation started...");
ProgressTracker.add(requestId, "Restore database operation started...");
ServerConnection connection = null;
try
{
var connectionString = configuration["MasterConnection"];
// Define a Restore object variable
Restore restore = new Restore();
// Specify the database name
restore.Database = "Northwind";
restore.Action = RestoreActionType.Database;
// Add the device that contains the full database backup to the Restore object
restore.Devices.AddDevice(backupFilePath, DeviceType.File);
// Set ReplaceDatabase = true to create new database regardless of the existence of specified database
restore.ReplaceDatabase = true;
// Set the NoRecovery property to False
// If you have a differential or log restore to be followed, you would specify NoRecovery = true
restore.NoRecovery = false;
restore.PercentCompleteNotification = 10;
// Setup a new connection to the data server
connection = new ServerConnection(new SqlConnection(connectionString));
Server sqlServer = new Server(connection);
restore.PercentComplete += (s, e) =>
{
// Inform the user percent restore
logger.LogInformation($"Percent Restore: {e.Percent}");
ProgressTracker.add(requestId, $"Percent Restore: {e.Percent}");
};
// Restore the full database backup with recovery
restore.SqlRestore(sqlServer);
var db = sqlServer.Databases[restore.Database];
db.SetOnline();
sqlServer.Refresh();
db.Refresh();
// Inform the user that the restore has been completed
logger.LogInformation("Restore database has been completed");
ProgressTracker.add(requestId, "Restore database has been completed");
}
finally
{
if (connection != null)
connection.Disconnect();
}
}
public static void PerformDataUpgrade(IConfiguration configuration, Guid requestId, ILogger <DatabaseUpgrater> logger)
{
string scriptsPath = GetScriptsDirectory("Data", configuration);
if (Directory.GetFiles(scriptsPath, "*.sql", SearchOption.TopDirectoryOnly).Length > 0)
{
logger.LogInformation("Start performing data upgrade...");
ProgressTracker.add(requestId, "Start performing data upgrade...");
var connectionString = configuration["NorthwindConnection"];
var scriptsExecutor =
DeployChanges.To
.SqlDatabase(connectionString)
.WithScriptsFromFileSystem
(
scriptsPath,
new FileSystemScriptOptions {
IncludeSubDirectories = false
}
)
.WithTransaction() // apply all changes in a single transaction
.LogToConsole();
var upgrader = scriptsExecutor.Build();
// Check if an upgrade is required
if (upgrader.IsUpgradeRequired())
{
var upgradeResult = upgrader.PerformUpgrade();
if (upgradeResult.Successful)
{
var message = "Database data upgraded successfully.";
logger.LogInformation(message);
ProgressTracker.add(requestId, message);
}
else
{
var message = "Error performing data upgrade.";
logger.LogError(message);
ProgressTracker.add(requestId, message);
throw new Exception($"{message}: ", upgradeResult.Error);
}
}
else
{
var message = "Data update is not required.";
logger.LogInformation(message);
ProgressTracker.add(requestId, message);
}
}
}
[AutomaticRetry(Attempts = 0)] // If you don’t want a job to be retried, place an explicit attribute with 0 maximum retry attempts value
public async Task PerformAsync(Guid requestId)
{
_logger.LogInformation("Process to upload database for {@requestId} is starting...", requestId);
var backupDirectory = _configuration["BackupDirectory"];
string backupFilePath = string.Empty;
try
{
var updateRequired = DeployDbChanges.IsUpgradeRequired(_configuration);
_cancellationToken.ThrowIfCancellationRequested();
if (updateRequired)
{
if (!Directory.Exists(backupDirectory))
{
if (!RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
backupDirectory = backupDirectory.Replace("\\", "//");
}
Directory.CreateDirectory(backupDirectory);
}
var backupFileName = $"Northwind_{ DateTime.Now:yyyy-MM-dd-HH-mm-ss}.bak";
backupFilePath = Path.Combine(backupDirectory, backupFileName);
backupFilePath = Path.GetFullPath(backupFilePath);
if (!RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
backupFilePath = backupFilePath.Replace("\\", "//");
}
_cancellationToken.ThrowIfCancellationRequested();
// Backup database
BackupAndRestoreDb.BackupDB(backupFilePath, _configuration, requestId, _logger);
_cancellationToken.ThrowIfCancellationRequested();
// Check if backup file created
if (!File.Exists(backupFilePath))
{
throw new Exception($"Backup file '{backupFilePath}' has not been created.");
}
// Perform database upgrade
DeployDbChanges.PerformUpgrade(_configuration, requestId, _logger);
// Message of completion
_logger.LogInformation("All background jobs are complete.");
ProgressTracker.add(requestId, "Complete"); // DO NOT CHANGE value "Complete". It uses by TaskProgress.cshtml
}
else
{
_logger.LogInformation("Upgrade is not required.");
ProgressTracker.add(requestId, "NotRequired"); // DO NOT CHANGE value "NotRequired". It uses by TaskProgress.cshtml
}
}
catch (Exception ex)
{
ProgressTracker.add(requestId, $"Error: {ex.Message}"); // DO NOT CHANGE value "Error". It uses by TaskProgress.cshtml
_logger.LogError(ex.Message);
if (!_cancellationToken.IsCancellationRequested)
{
try
{
if (!string.IsNullOrEmpty(backupFilePath) && File.Exists(backupFilePath))
{
// Restore databse
BackupAndRestoreDb.RestoreDB(backupFilePath, _configuration, requestId, _logger);
}
}
catch (Exception e)
{
ProgressTracker.add(requestId, $"Error: {e.Message}"); // DO NOT CHANGE value "Error". It uses by TaskProgress.cshtml
_logger.LogError(e.Message);
}
}
}
// Remove the backup files from the hard disk
if (!string.IsNullOrEmpty(backupFilePath) && File.Exists(backupFilePath))
{
File.Delete(backupFilePath);
}
await Task.CompletedTask;
}
public static void BackupDB(string backupFilePath, IConfiguration configuration, Guid requestId, ILogger<DatabaseUpgrater> logger)
{
logger.LogInformation("Backup database operation started...");
ProgressTracker.add(requestId, "Backup database operation started...");
ServerConnection connection = null;
try
{
var connectionString = configuration["MasterConnection"];
// Define a Backup object variable
Backup backup = new Backup();
// Set type of backup to be performed to database
backup.Action = BackupActionType.Database;
backup.BackupSetDescription = "Full backup of Northwind";
// Set the name used to identify a particular backup set
backup.BackupSetName = "Northwind Backup";
// Specify the name of the database to back up
backup.Database = "Northwind";
// Set up the backup device to use filesystem
BackupDeviceItem deviceItem = new BackupDeviceItem(backupFilePath, DeviceType.File);
// Add the device to the Backup object
backup.Devices.Add(deviceItem);
// Setup a new connection to the data server
connection = new ServerConnection(new SqlConnection(connectionString));
Server sqlServer = new Server(connection);
// Initialize devices associated with a backup operation
backup.Initialize = true;
backup.Checksum = true;
// Set it to true to have the process continue even after checksum error
backup.ContinueAfterError = true;
// Set the Incremental property to False to specify that this is a full database backup
backup.Incremental = false;
// Set the backup expiration date
backup.ExpirationDate = DateTime.Now.AddYears(1);
// Specify that the log must be truncated after the backup is complete
backup.LogTruncation = BackupTruncateLogType.Truncate;
backup.PercentCompleteNotification = 10;
backup.PercentComplete += (s, e) =>
{
// Inform the user percent complete
logger.LogInformation($"Percent Complete: {e.Percent}");
ProgressTracker.add(requestId, $"Percent Complete: {e.Percent}");
};
// Run SqlBackup to perform the full database backup on the instance of SQL Server
backup.SqlBackup(sqlServer);
// Inform the user that the backup has been completed
logger.LogInformation("Backup database has been completed");
ProgressTracker.add(requestId, "Backup database has been completed");
}
finally
{
if (connection != null)
connection.Disconnect();
}
}