public static void RestoreMultipleSqlBackup(
this ICakeContext context,
string connectionString,
RestoreSqlBackupSettings settings,
IList <FilePath> backupFiles,
IList <FilePath>?differentialBackupFiles = null)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
RestoreSqlBackupImpl.RestoreSqlBackup(context, connectionString, settings, backupFiles, differentialBackupFiles: differentialBackupFiles);
}
// if database name is not provided, dbname from the backup is used.
// if newStoragePath is not provided, system defaults are used
internal static void RestoreSqlBackup(ICakeContext context, String connectionString, FilePath backupFile, RestoreSqlBackupSettings settings)
{
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
var oldDbName = GetDatabaseName(backupFile, connection);
var newDatabaseName = settings.NewDatabaseName ?? oldDbName;
context.Log.Information($"Using database name '{newDatabaseName}' to be a name for the restored database");
var logicalNames = GetLogicalNames(backupFile, connection);
var sql = "";
if (settings.SwitchToSingleUserMode)
{
sql += $@"
if db_id({Sql.EscapeNameQuotes(newDatabaseName)}) is not null
begin
use master;
alter database {Sql.EscapeName(newDatabaseName)} set single_user with rollback immediate;
end
";
}
sql += $"Restore database {Sql.EscapeName(newDatabaseName)} from disk = @backupFile with ";
for (var i = 0; i < logicalNames.Count; i++)
{
sql += $" move @LName{i} to @LPath{i}";
if (i < logicalNames.Count - 1)
{
sql += ", \r\n"; // only need comma before penultimate list
}
}
if (settings.WithReplace)
{
sql += ",\r\n replace ";
}
sql += $";\r\n alter database {Sql.EscapeName(newDatabaseName)} set multi_user;";
context.Log.Debug($"Executing SQL : {sql}");
var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection);
command.Parameters.AddWithValue("@backupFile", backupFile.ToString());
for (var i = 0; i < logicalNames.Count; i++)
{
var lParameterName = "@LName" + i;
context.Log.Debug($"Adding parameter '{lParameterName}' with value '{logicalNames[i].LogicalName}'");
command.Parameters.AddWithValue(lParameterName, logicalNames[i].LogicalName);
var filePath = GetFilePath(connection, oldDbName, newDatabaseName, settings.NewStorageFolder, logicalNames[i]);
var pathParamName = "@LPath" + i;
context.Log.Debug($"Adding parameter '{pathParamName}' with value '{filePath}'");
command.Parameters.AddWithValue(pathParamName, filePath);
}
command.ExecuteNonQuery();
}
}
public static void RestoreSqlBackup(this ICakeContext context, String connectionString, FilePath backupFile, RestoreSqlBackupSettings settings)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(backupFile, nameof(backupFile));
SqlBackupsImpl.RestoreSqlBackup(context, connectionString, backupFile, settings);
}
// if database name is not provided, dbname from the backup is used.
// if newStoragePath is not provided, system defaults are used
internal static void RestoreSqlBackup(ICakeContext context, String connectionString, RestoreSqlBackupSettings settings, IList <FilePath> backupFiles, IList <FilePath> differentialBackupFiles = null)
{
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
var firstBackupFile = backupFiles.First();
var oldDbName = GetDatabaseName(firstBackupFile, connection);
var databaseName = settings.NewDatabaseName ?? oldDbName;
if (settings.SwitchToSingleUserMode)
{
var singleModeCommand = GetSetDatabaseSingleUserModeCommand(context, connection, databaseName, true);
singleModeCommand.ExecuteNonQuery();
}
var hasDifferentialBackup = differentialBackupFiles != null && differentialBackupFiles.Any();
var fullRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.BackupSetFile,
settings.WithReplace,
hasDifferentialBackup,
databaseName,
settings.NewStorageFolder,
backupFiles.ToArray());
fullRestoreCommand.ExecuteNonQuery();
if (hasDifferentialBackup)
{
var differentialRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.DifferentialBackupSetFile,
false,
false,
databaseName,
settings.NewStorageFolder,
differentialBackupFiles.ToArray());
differentialRestoreCommand.ExecuteNonQuery();
}
if (settings.SwitchToSingleUserMode)
{
var singleModeCommand = GetSetDatabaseSingleUserModeCommand(context, connection, databaseName, false);
singleModeCommand.ExecuteNonQuery();
}
}
}
// if database name is not provided, dbname from the backup is used.
// if newStoragePath is not provided, system defaults are used
internal static void RestoreSqlBackup(
ICakeContext context,
string connectionString,
RestoreSqlBackupSettings settings,
IList <FilePath> backupFiles,
IList <FilePath>?differentialBackupFiles = null)
{
Initializer.InitializeNativeSearchPath();
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
var firstBackupFile = backupFiles.First();
var oldDbName = GetDatabaseName(firstBackupFile, connection);
var databaseName = settings.NewDatabaseName ?? oldDbName;
if (settings.SwitchToUserMode != DbUserMode.MultiUser)
{
using (var singleModeCommand = GetSetDatabaseUserModeCommand(
context,
connection,
databaseName,
settings.SwitchToUserMode))
{
singleModeCommand.ExecuteNonQuery();
}
}
var hasDifferentialBackup = differentialBackupFiles?.Count > 0;
using (var fullRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.BackupSetFile,
settings.WithReplace,
hasDifferentialBackup,
databaseName,
settings.NewStorageFolder ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
backupFiles.ToArray()))
{
fullRestoreCommand.ExecuteNonQuery();
}
if (hasDifferentialBackup)
{
using (var differentialRestoreCommand = GetRestoreSqlBackupCommand(
context,
connection,
settings.DifferentialBackupSetFile,
false,
false,
databaseName,
settings.NewStorageFolder ?? Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
differentialBackupFiles?.ToArray() ?? Array.Empty <FilePath>()))
{
differentialRestoreCommand.ExecuteNonQuery();
}
}
if (settings.SwitchToUserMode != DbUserMode.MultiUser)
{
using (var singleModeCommand =
GetSetDatabaseUserModeCommand(context, connection, databaseName, DbUserMode.MultiUser))
{
singleModeCommand.ExecuteNonQuery();
}
}
}
}