/// <summary>
/// Restores each of the backups. If the default file location is available it will move the files to there.
/// </summary>
/// <param name="backupOrder">An ordered list of backups to apply.</param>
/// <param name="databaseName">Database to restore to.</param>
/// <param name="retryDurationProvider">
/// Retry duration function. Retry 10 times, input retry number, output timespan to
/// wait
/// </param>
/// <param name="fileRelocation">Option for renaming files during the restore.</param>
public void Restore(IEnumerable <BackupMetadata> backupOrder, string databaseName,
Func <int, TimeSpan> retryDurationProvider,
Func <string, string> fileRelocation = null)
{
var policy = Policy
.Handle <ExecutionFailureException>(e => e.InnerException != null &&
e.InnerException is SqlException &&
e.InnerException.Message
.Contains("The process cannot access the file because it is being used by another process"))
.WaitAndRetry(10, retryDurationProvider);
var restore = new Restore {
Database = databaseName, NoRecovery = true
};
foreach (var backup in backupOrder)
{
var device = BackupFileTools.IsValidFileUrl(backup.PhysicalDeviceName) ? DeviceType.Url : DeviceType.File;
var backupDeviceItem = new BackupDeviceItem(backup.PhysicalDeviceName, device);
if (_credentialName != null && device == DeviceType.Url)
{
backupDeviceItem.CredentialName = _credentialName;
}
restore.Devices.Add(backupDeviceItem);
var defaultFileLocations = DefaultFileLocations();
if (defaultFileLocations != null)
{
restore.RelocateFiles.Clear();
var fileList = policy.Execute(() => restore.ReadFileList(_server).AsEnumerable());
foreach (var file in fileList)
{
var physicalName = (string)file["PhysicalName"];
var fileName = GetFileName(physicalName);
if (fileRelocation != null)
{
fileName = fileRelocation(fileName);
}
var path = (string)file["Type"] == "L" ? defaultFileLocations?.Log : defaultFileLocations?.Data;
path ??= Path.GetFullPath(physicalName);
var newFilePath = Path.Combine(path, fileName);
restore.RelocateFiles.Add(new RelocateFile((string)file["LogicalName"], newFilePath));
}
}
_server.ConnectionContext.StatementTimeout = 86400; // 60 * 60 * 24 = 24 hours
policy.Execute(() => restore.SqlRestore(_server));
restore.Devices.Remove(backupDeviceItem);
}
}
/// <summary>
/// Restores each of the backups. If the default file location is available it will move the files to there.
/// </summary>
/// <param name="backupOrder">An ordered list of backups to apply.</param>
/// <param name="databaseName">Database to restore to.</param>
/// <param name="fileRelocation">Option for renaming files during the restore.</param>
public void Restore(IEnumerable <BackupMetadata> backupOrder, string databaseName,
Func <string, string> fileRelocation = null)
{
var restore = new Restore {
Database = databaseName, NoRecovery = true
};
foreach (var backup in backupOrder)
{
var device = BackupFileTools.IsUrl(backup.PhysicalDeviceName) ? DeviceType.Url : DeviceType.File;
var backupDeviceItem = new BackupDeviceItem(backup.PhysicalDeviceName, device);
if (_credentialName != null && device == DeviceType.Url)
{
backupDeviceItem.CredentialName = _credentialName;
}
restore.Devices.Add(backupDeviceItem);
var defaultFileLocations = DefaultFileLocations();
if (defaultFileLocations != null)
{
restore.RelocateFiles.Clear();
foreach (var file in restore.ReadFileList(_server).AsEnumerable())
{
var physicalName = (string)file["PhysicalName"];
var fileName = Path.GetFileName(physicalName) ??
throw new InvalidBackupException($"Physical name in backup is incomplete: {physicalName}");
if (fileRelocation != null)
{
fileName = fileRelocation(fileName);
}
var path = (string)file["Type"] == "L" ? defaultFileLocations?.Log : defaultFileLocations?.Data;
path = path ?? Path.GetFullPath(physicalName);
var newFilePath = Path.Combine(path, fileName);
restore.RelocateFiles.Add(new RelocateFile((string)file["LogicalName"], newFilePath));
}
}
_server.ConnectionContext.StatementTimeout = 86400; // 60 * 60 * 24 = 24 hours
restore.SqlRestore(_server);
restore.Devices.Remove(backupDeviceItem);
}
}
private void Backup(Backup backup, string backupDirectoryPathQuery, string databaseName,
BackupFileTools.BackupType type)
{
var backupDirectory = BackupDirectoryOrDefault(backupDirectoryPathQuery);
var filePath =
$"{backupDirectory}/{databaseName}_backup_{DateTime.Now.ToString("yyyy_MM_dd_hhmmss_fff")}.{BackupFileTools.BackupTypeToExtension(type)}";
var deviceType = BackupFileTools.IsUrl(filePath) ? DeviceType.Url : DeviceType.File;
var bdi = new BackupDeviceItem(filePath, deviceType);
if (_credentialName != null && deviceType == DeviceType.Url)
{
bdi.CredentialName = _credentialName;
}
backup.Devices.Add(bdi);
backup.SqlBackup(_server);
}
/// <summary>
/// Queries msdb on the instance for backups of this database.
/// </summary>
/// <returns>A list of backups known by msdb</returns>
public List <BackupMetadata> RecentBackups()
{
var backups = new List <BackupMetadata>();
var query = "SELECT s.database_name, m.physical_device_name, s.backup_start_date, s.first_lsn, s.last_lsn," +
"s.database_backup_lsn, s.checkpoint_lsn, s.[type] AS backup_type, s.server_name, s.recovery_model " +
"FROM msdb.dbo.backupset s " +
"INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id " +
"WHERE s.last_lsn >= (" +
"SELECT MAX(last_lsn) FROM msdb.dbo.backupset " +
"WHERE [type] = 'D' " +
"AND database_name = @dbName " +
"AND is_copy_only = 0" +
") " +
"AND s.database_name = @dbName " +
"AND is_copy_only = 0" +
"ORDER BY s.backup_start_date DESC, backup_finish_date";
using var cmd = _server.SqlConnection.CreateCommand();
cmd.CommandText = query;
var dbName = cmd.CreateParameter();
dbName.ParameterName = "dbName";
dbName.Value = _database.Name;
cmd.Parameters.Add(dbName);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
backups.Add(new BackupMetadata {
CheckpointLsn = (decimal)reader["checkpoint_lsn"],
DatabaseBackupLsn = (decimal)reader["database_backup_lsn"],
DatabaseName = (string)reader["database_name"],
FirstLsn = (decimal)reader["first_lsn"],
LastLsn = (decimal)reader["last_lsn"],
PhysicalDeviceName = (string)reader["physical_device_name"],
ServerName = (string)reader["server_name"],
StartTime = (DateTime)reader["backup_start_date"],
BackupType = BackupFileTools.BackupTypeAbbrevToType((string)reader["backup_type"])
});
}
return(backups);
}