public static SqlConnection OpenSqlConnection(this ICakeContext context, String connectionString)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
return(SqlServerAliasesImpl.OpenSqlConnection(context, connectionString));
}
internal static void Execute(ICakeContext context, string connectionString, string databaseName, BackupDatabaseSettings settings)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(settings, nameof(settings));
var backupFile = GetBackupFileName(databaseName, settings.Path);
var compress = settings.Compress ? ", COMPRESSION" : string.Empty;
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
context.Log.Information($"Backing up database '{databaseName}' to {backupFile}");
var sql = $@"
BACKUP DATABASE {Sql.EscapeName(databaseName)}
TO DISK = @backupFile
WITH FORMAT, INIT, COPY_ONLY, NAME = '{databaseName} Full Backup',
SKIP, REWIND, NOUNLOAD, STATS = 10 {compress}";
context.Log.Information(sql);
var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection);
command.Parameters.AddWithValue("@backupFile", backupFile);
command.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, 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 pathSeparator = GetPlatformPathSeparator(connection);
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], pathSeparator);
var pathParamName = "@LPath" + i;
context.Log.Debug($"Adding parameter '{pathParamName}' with value '{filePath}'");
command.Parameters.AddWithValue(pathParamName, filePath);
}
command.ExecuteNonQuery();
}
}
internal static void SetDatabaseSingleUserMode(ICakeContext context, String connectionString, String databaseName, bool singleUserMode)
{
using (var connection = SqlServerAliasesImpl.OpenSqlConnection(context, connectionString))
{
var command = GetSetDatabaseSingleUserModeCommand(context, connection, databaseName, singleUserMode);
command.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)
{
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();
}
}
}
}