Copy remote MSSQL DB to local server
I faced with one issue how to automatically download and restore databases from remote server to my local MSSQL server. It sounds like very generic task so I tried to find a solution in Internet without success. So there is a small console application which does the next things:
- Backup selected databases on remote server;
- Copy backups to local folder;
- Restore copied databases to local MSSQL;
- Change the owner of databases to current user;
A small snippet with implementation is below.
/*
You need to add next NuGet packages:
"Microsoft.Extensions.Configuration": "2.0.0",
"Microsoft.Extensions.Configuration.FileExtensions": "2.0.0",
"Microsoft.Extensions.Configuration.Json": "2.0.0",
"System.Data.Common": "4.3.0",
"System.Data.SqlClient": "4.4.2",
"System.Runtime": "4.3.0"
*/
class Program
{
private static IConfigurationRoot Configuration = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json").Build();
private static string[] dbs { get; } = Configuration.GetSection("dbs")
.GetChildren().Select(x=>x.Value).ToArray();
private static string remoteServer { get; } = Configuration["connectionStrings:remoteServer"];
private static string localServer { get; } = Configuration["connectionStrings:localServer"];
private static string currentUser { get; } = Configuration["currentUser"];
private static string remoteServerPathWithBackups { get; } = Configuration["remoteServerPathWithBackups"];
private static string serverPathWithBackups { get; } = Configuration["serverPathWithBackups"];
private static string userNameToLogInToServer { get; } = Configuration["userNameToLogInToServer"];
private static string passwordToLogInToServer { get; } = Configuration["passwordToLogInToServer"];
private static string localPathToCopyBackups { get; } = Configuration["localPathToCopyBackups"];
private static string backupFileName = Configuration["backupFileName"];
private const string changeOwnerOfDb = @"
USE {0}
EXEC sp_changedbowner '{1}'";
private const string changeToSingleUser = @"
ALTER DATABASE[{0}] SET Single_User WITH Rollback Immediate";
private const string changeToMultiUser = @"
ALTER DATABASE [{0}] SET Multi_User";
private const string AddNetPath = "NET USE {0} {1} /USER:{2}";
private const string DeleteNetPath = "NET USE {0} /DELETE";
private const string CopyReport = "robocopy {0} {1} {2}";
private const string restoreDb = @"
RESTORE DATABASE {0} FROM DISK = '{1}' WITH REPLACE, RECOVERY;";
private const string backupDb = @"
BACKUP DATABASE {0} TO DISK='{1}' WITH INIT, COPY_ONLY, FORMAT, COMPRESSION;";
static void Main(string[] args)
{
foreach (var db in dbs)
{
Console.WriteLine($"Backup {db}");
BackupTargetDatabase(remoteServer, db);
Console.WriteLine($"Copy created backup to local - {db}");
CopyBackupToLocal(db);
Console.WriteLine($"Change to single user mode - {db}");
ChangeToSingleUser(localServer, db);
Console.WriteLine($"Restore db locally - {db}");
RestoreTargetDatabase(localServer, db);
Console.WriteLine($"Change the owner - {db}");
ChangeOwnerOfDb(localServer, db);
Console.WriteLine($"Reset to multi user mode - {db}");
ChangeToMultiUser(localServer, db);
}
}
private static void ChangeToMultiUser(string localServer, string db) =>
ExecuteSqlCommand(localServer, string.Format(changeToMultiUser, db));
private static void ChangeToSingleUser(string localServer, string db) =>
ExecuteSqlCommand(localServer, string.Format(changeToSingleUser, db));
private static void ChangeOwnerOfDb(string localServer, string db) =>
ExecuteSqlCommand(localServer, string.Format(changeOwnerOfDb, db, currentUser));
private static void CopyBackupToLocal(string database)
{
ExecuteCommand(string.Format(AddNetPath, remoteServerPathWithBackups, passwordToLogInToServer, userNameToLogInToServer));
ExecuteCommand(string.Format(CopyReport, remoteServerPathWithBackups, localPathToCopyBackups, string.Format(backupFileName, database)));
ExecuteCommand(string.Format(DeleteNetPath, remoteServerPathWithBackups));
}
private static void ExecuteCommand(string command)
{
var processInfo = new ProcessStartInfo("cmd.exe", "/c " + command);
processInfo.CreateNoWindow = true;
processInfo.UseShellExecute = false;
processInfo.RedirectStandardError = true;
processInfo.RedirectStandardOutput = true;
Console.WriteLine(command);
var process = Process.Start(processInfo);
process.OutputDataReceived += (object sender, DataReceivedEventArgs e) =>
Console.WriteLine($"{e.Data}");
process.BeginOutputReadLine();
process.ErrorDataReceived += (object sender, DataReceivedEventArgs e) =>
Console.WriteLine($"{e.Data}");
process.BeginErrorReadLine();
process.WaitForExit();
if (process.ExitCode > 0)
Console.WriteLine($"Error {nameof(process.ExitCode)}: {process.ExitCode}");
process.Close();
}
private static void RestoreTargetDatabase(string connectionString, string database) =>
ExecuteSqlCommand(connectionString, string.Format(restoreDb, database, Path.Combine(localPathToCopyBackups, string.Format(backupFileName, database))));
private static void BackupTargetDatabase(string connectionString, string database) =>
ExecuteSqlCommand(connectionString, string.Format(backupDb, database, Path.Combine(serverPathWithBackups, string.Format(backupFileName, database))));
private static void ExecuteSqlCommand(string connectionString, string query)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(query, connection))
{
connection.Open();
command.CommandTimeout = 300;
command.ExecuteNonQuery();
}
}
}
}
Thanks.