private static SqlCommand GetSetDatabaseSingleUserModeCommand(ICakeContext context, SqlConnection connection, String databaseName, bool singleUserMode)
{
var sb = new StringBuilder();
sb.AppendLine();
if (singleUserMode)
{
sb.AppendLine($@"if db_id({Sql.EscapeNameQuotes(databaseName)}) is not null");
sb.AppendLine($@"begin");
sb.AppendLine($@" use master;");
sb.AppendLine($@" alter database {Sql.EscapeName(databaseName)} set single_user with rollback immediate;");
sb.AppendLine($@"end");
sb.AppendLine($@";");
}
else
{
sb.AppendLine($@"if db_id({Sql.EscapeNameQuotes(databaseName)}) is not null");
sb.AppendLine($@"begin");
sb.AppendLine($@" use master;");
sb.AppendLine($@" alter database {Sql.EscapeName(databaseName)} set multi_user;");
sb.AppendLine($@"end");
sb.AppendLine($@";");
}
context.Log.Debug($"Executing SQL : {sb}");
var command = SqlServerAliasesImpl.CreateSqlCommand(sb.ToString(), connection);
return(command);
}
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();
}
}
private static string ReadSingleString(string sql, string columnName, SqlConnection connection)
{
using (var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection))
{
return(ReadSingleString(command, columnName));
}
}
public static SqlConnection OpenSqlConnection(this ICakeContext context, String connectionString)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
return(SqlServerAliasesImpl.OpenSqlConnection(context, connectionString));
}
// 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 List <LogicalNames> GetLogicalNames(FilePath backupFile, SqlConnection connection)
{
var fileListSql = @"RESTORE FILELISTONLY from Disk = @backupFile";
var command = SqlServerAliasesImpl.CreateSqlCommand(fileListSql, connection);
command.Parameters.AddWithValue("@backupFile", backupFile.ToString());
var logicalNames = new List <LogicalNames>();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
{
throw new Exception($"Unable to read logical names from the backup at {backupFile}. Are you sure it is SQL backup file?");
}
while (reader.Read())
{
var name = new LogicalNames()
{
LogicalName = reader.GetString(reader.GetOrdinal("LogicalName")),
PhysicalName = reader.GetString(reader.GetOrdinal("PhysicalName")),
Type = reader.GetString(reader.GetOrdinal("Type")),
};
logicalNames.Add(name);
}
}
return(logicalNames);
}
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();
}
}
public static bool DatabaseExists(this ICakeContext context, String connectionString, String databaseName)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(databaseName, nameof(databaseName));
return(SqlServerAliasesImpl.DatabaseExists(context, connectionString, databaseName));
}
public static void ExecuteSqlFile(this ICakeContext context, SqlConnection connection, FilePath sqlFile)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connection, nameof(connection));
Guard.ArgumentIsNotNull(sqlFile, nameof(sqlFile));
SqlServerAliasesImpl.ExecuteSqlFile(context, connection, sqlFile);
}
public static void CreateDatabase(this ICakeContext context, String connectionString, String databaseName)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(databaseName, nameof(databaseName));
SqlServerAliasesImpl.CreateDatabase(context, connectionString, databaseName);
}
public static void ExecuteSqlCommand(this ICakeContext context, SqlConnection connection, string sqlCommands)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connection, nameof(connection));
Guard.ArgumentIsNotNull(sqlCommands, nameof(sqlCommands));
SqlServerAliasesImpl.ExecuteSqlCommand(context, connection, sqlCommands);
}
public static void DropAndCreateDatabase(this ICakeContext context, String connectionString, String databaseName, CreateDatabaseSettings settings)
{
Guard.ArgumentIsNotNull(context, nameof(context));
Guard.ArgumentIsNotNull(connectionString, nameof(connectionString));
Guard.ArgumentIsNotNull(databaseName, nameof(databaseName));
Guard.ArgumentIsNotNull(settings, nameof(settings));
SqlServerAliasesImpl.DropAndCreateDatabase(context, connectionString, databaseName, settings);
}
internal static string GetDatabaseName(FilePath backupFile, SqlConnection connection)
{
const string sql = "restore headeronly from disk = @backupFile";
using (var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@backupFile", backupFile.ToString());
return(ReadSingleString(command, "DatabaseName"));
}
}
internal static String GetDatabaseName(FilePath backupFile, SqlConnection connection)
{
var sql = @"restore headeronly from disk = @backupFile";
var command = SqlServerAliasesImpl.CreateSqlCommand(sql, connection);
command.Parameters.AddWithValue("@backupFile", backupFile.ToString());
var dbName = ReadSingleString(command, "DatabaseName");
return(dbName);
}
// 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();
}
}
}
private static SqlCommand GetSetDatabaseUserModeCommand(ICakeContext context, SqlConnection connection, string databaseName, DbUserMode userMode)
{
var sb = new StringBuilder();
sb.AppendLine();
switch (userMode)
{
case DbUserMode.SingleUser:
sb.Append("if db_id(").Append(Sql.EscapeNameQuotes(databaseName)).AppendLine(") is not null");
sb.AppendLine("begin");
sb.AppendLine(" use master;");
sb.Append(" alter database ").Append(Sql.EscapeName(databaseName)).AppendLine(" set single_user with rollback immediate;");
sb.AppendLine("end");
sb.AppendLine(";");
break;
case DbUserMode.RestrictedUser:
sb.Append("if db_id(").Append(Sql.EscapeNameQuotes(databaseName)).AppendLine(") is not null");
sb.AppendLine("begin");
sb.AppendLine(" use master;");
sb.Append(" alter database ").Append(Sql.EscapeName(databaseName)).AppendLine(" set restricted_user with rollback immediate;");
sb.AppendLine("end");
sb.AppendLine(";");
break;
default:
sb.Append("if db_id(").Append(Sql.EscapeNameQuotes(databaseName)).AppendLine(") is not null");
sb.AppendLine("begin");
sb.AppendLine(" use master;");
sb.Append(" alter database ").Append(Sql.EscapeName(databaseName)).AppendLine(" set multi_user;");
sb.AppendLine("end");
sb.AppendLine(";");
break;
}
context.Log.Debug($"Executing SQL : {sb}");
var command = SqlServerAliasesImpl.CreateSqlCommand(sb.ToString(), connection);
return(command);
}
public static void SetSqlCommandTimeout(this ICakeContext context, int commandTimeout)
{
SqlServerAliasesImpl.SetSqlCommandTimeout(context, commandTimeout);
}
// This method is used to determine one single "Restore database" command.
private static SqlCommand GetRestoreSqlBackupCommand(
ICakeContext context,
SqlConnection connection,
int?backupSetFile,
bool withReplace,
bool withNoRecovery,
string newDatabaseName,
DirectoryPath newStorageFolder,
params FilePath[] backupFiles)
{
var firstBackupFile = backupFiles.First();
var oldDbName = GetDatabaseName(firstBackupFile, connection);
var databaseName = newDatabaseName ?? oldDbName;
context.Log.Information($"Using database name '{databaseName}' to be a name for the restored database");
var logicalNames = GetLogicalNames(firstBackupFile, connection);
var sb = new StringBuilder();
sb.AppendLine();
sb.AppendLine($"Restore database {Sql.EscapeName(databaseName)}");
sb.AppendLine($"from");
for (var i = 0; i < backupFiles.Length; i++)
{
// only need comma before penultimate list
var trailingComma = i < backupFiles.Length - 1 ? "," : "";
sb.AppendLine($" disk = @backupFile{i}{trailingComma}");
}
sb.AppendLine($"with");
if (backupSetFile.HasValue)
{
sb.AppendLine($" file = {backupSetFile.Value},");
}
for (var i = 0; i < logicalNames.Count; i++)
{
sb.AppendLine($" move @LName{i} to @LPath{i},");
}
if (withReplace)
{
sb.AppendLine($" replace,");
}
if (withNoRecovery)
{
sb.AppendLine($" norecovery");
}
else
{
sb.AppendLine($" recovery");
}
context.Log.Debug($"Executing SQL : {sb}");
var pathSeparator = GetPlatformPathSeparator(connection);
var command = SqlServerAliasesImpl.CreateSqlCommand(sb.ToString(), connection);
for (var i = 0; i < backupFiles.Length; i++)
{
command.Parameters.AddWithValue($"@backupFile{i}", backupFiles[i].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, databaseName, newStorageFolder, logicalNames[i], pathSeparator);
var pathParamName = "@LPath" + i;
context.Log.Debug($"Adding parameter '{pathParamName}' with value '{filePath}'");
command.Parameters.AddWithValue(pathParamName, filePath);
}
return(command);
}
// 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();
}
}
}
}