private static void Execute()
{
var localInfo = SqlLocalDbApi.GetInstanceInfo(AppConfig.Database);
if (localInfo.Exists)
{
SqlLocalDbApi.StopInstance(localInfo.Name);
SqlLocalDbApi.DeleteInstance(localInfo.Name, true);
}
SqlLocalDbApi.CreateInstance(localInfo.Name);
using (var memoryStream = new MemoryStream())
{
var remote = new DacServices(AppConfig.RemoteConnectionString);
remote.ProgressChanged += (sender, args) => Console.WriteLine($"remote {args.Status} {args.Message}");
remote.ExportBacpac(memoryStream, localInfo.Name, DacSchemaModelStorageType.Memory);
using (var bacPackage = BacPackage.Load(memoryStream, DacSchemaModelStorageType.Memory))
{
var local = new DacServices(AppConfig.LocalConnectionString);
local.ProgressChanged += (sender, args) => Console.WriteLine($"local {args.Status} {args.Message}");
local.ImportBacpac(bacPackage, localInfo.Name);
}
}
}
/// <summary>
/// Restores the backup of the database
/// </summary>
/// <remarks>The process of the bacpac restoration consists in drop the database from the server and restores the bacpac. This is because the bacpac restoration needs an
/// empty database
/// </remarks>
public static void RestoreDatabaseBackup()
{
try
{
var connectionString = System.Configuration.ConfigurationManager.AppSettings["AzureRestoreConnectionString"];
var tempPathForbacpac = Path.Combine(Path.GetTempPath(), "azureBackup");
var filepath = Path.Combine(tempPathForbacpac, "data.bac");
//Trace.WriteLine("SQL Scanner - Azure database backup location: " + filepath, "Information");
if (File.Exists(filepath))
{
// Database deletion (using admin user and connecting to the master database)
CleanupDatabase(connectionString);
// Uploading bacpac (using admin user and connecting to the user database)
var dacSvc = new DacServices(connectionString);
var package = BacPackage.Load(filepath);
dacSvc.ImportBacpac(package, System.Configuration.ConfigurationManager.AppSettings["TestRestoreDBName"]);
}
}
catch (Exception e)
{
throw new ApplicationException(string.Format("An error occurred while restoring the database backup: {0}", e.Message), e);
}
}
static void BACPACからDB作る()
{
var dac = new DacServices(connstr);
using (var package = BacPackage.Load("d:/dotnetconf2019.bacpac"))
{
dac.ImportBacpac(package, "new_dotnetconf2019");
}
}
private static int FileToDatabaseSync(F2dbOptions opts)
{
if (string.IsNullOrEmpty(opts.WorkingDirectory))
{
opts.WorkingDirectory = Environment.CurrentDirectory;
}
var db = opts.Database;
var pk = BacPackage.Load(opts.InputFile);
var spec = new DacAzureDatabaseSpecification
{
Edition = DacAzureEdition.Basic
};
using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
using (var singleUserCmd = new SqlCommand($"IF db_id('{db}') is not null ALTER DATABASE [{db}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", sqlConn))
using (var dropCmd = new SqlCommand($"IF db_id('{db}') is not null DROP DATABASE [{db}]", sqlConn))
{
sqlConn.Open();
singleUserCmd.ExecuteNonQuery();
dropCmd.ExecuteNonQuery();
}
var local = new DacServices(opts.OutputConnectionString);
local.ProgressChanged += (sender, eventArgs) => { Console.WriteLine($"[{db}] {eventArgs.Message}"); };
local.ImportBacpac(pk, db, spec);
if (!string.IsNullOrEmpty(opts.LocalUser))
{
using (var sqlConn = new SqlConnection(opts.OutputConnectionString))
using (var loginCmd = new SqlCommand($"USE [{db}]; CREATE USER [{opts.LocalUser}] FOR LOGIN [{opts.LocalUser}]; USE [{db}]; ALTER ROLE [db_owner] ADD MEMBER [{opts.LocalUser}];", sqlConn))
{
sqlConn.Open();
try
{
loginCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine($"WARNING: Couldn't add user {opts.LocalUser} because: {ex.Message}");
}
}
}
Console.Write("done.");
Console.WriteLine();
return(0);
}
public static SqlTestDB CreateFromBacpac(InstanceInfo instance, string bacpacPath, DacImportOptions importOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(bacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (BacPackage bp = BacPackage.Load(bacpacPath, DacSchemaModelStorageType.Memory))
{
importOptions = FillDefaultImportOptionsForTest(importOptions);
ds.ImportBacpac(bp, dbName, importOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return(sqlDb);
}
public static void RestoreBacpac(ICakeContext context, string connectionString, string newDatabaseName, string bacpacFilePath)
{
context.Log.Information($"About to restore bacpac from {bacpacFilePath} into database {newDatabaseName}");
var bacPackage = BacPackage.Load(bacpacFilePath);
context.Log.Debug($"Loaded bacpac file {bacpacFilePath}");
var service = new DacServices(connectionString);
service.ImportBacpac(bacPackage, newDatabaseName);
context.Log.Information($"Finished restoring bacpac file into database {newDatabaseName}");
}
private static void ImportBacpac(
string connectionString,
string targetDatabaseName,
string bacpacFileFullPath)
{
var ds = new DacServices(connectionString);
using var package = BacPackage.Load(bacpacFileFullPath);
var options = new DacImportOptions
{
CommandTimeout = DefaultCommandTimeout
};
ds.Message += (object sender, DacMessageEventArgs eventArgs) => Console.WriteLine(eventArgs.Message.Message);
ds.ImportBacpac(package, targetDatabaseName, options);
}
public async Task Import(ProgressDialogController progress)
{
await Task.Run(() =>
{
// open file
var result = new OpenFileDialogService().Show(fileName =>
{
// initialize LocalDB
Delete();
Create();
// import bacpac
_dacService.ProgressChanged += (sender, args) => progress.SetMessage($"{args.Status} : {args.Message}");
_dacService.ImportBacpac(BacPackage.Load(fileName), _databaseName);
});
progress.SetMessage(result ? "Success" : "Fail");
});
await progress.CloseAsync();
}
public static void RestoreBacpac(ICakeContext context, string connectionString, string newDatabaseName, string?bacpacFilePath)
{
if (string.IsNullOrEmpty(bacpacFilePath))
{
throw new ArgumentNullException(nameof(bacpacFilePath));
}
Initializer.InitializeNativeSearchPath();
context.Log.Information($"About to restore bacpac from {bacpacFilePath} into database {newDatabaseName}");
using (var bacPackage = BacPackage.Load(bacpacFilePath))
{
context.Log.Debug($"Loaded bacpac file {bacpacFilePath}");
var service = new DacServices(connectionString);
service.ImportBacpac(bacPackage, newDatabaseName);
}
context.Log.Information($"Finished restoring bacpac file into database {newDatabaseName}");
}
private void SQLDBBacPacImportFromFile(DacAzureEdition edition, bool importBlockOnPossibleDataLoss, string importDBConnectionString, string importDBName, string importFileFullPath)
{
DacDeployOptions dacOptions = new DacDeployOptions();
dacOptions.BlockOnPossibleDataLoss = importBlockOnPossibleDataLoss;
DacServices dacServiceInstance = new DacServices(importDBConnectionString);
// There are two events to get feed back during import.
//dacServiceInstance.Message += EventHandlerDACMessage;
//dacServiceInstance.ProgressChanged += EventHandlerDacServiceInstanceProgressChanged;
using (BacPackage dacpac = BacPackage.Load(importFileFullPath))
{
DacAzureDatabaseSpecification dbSpec = new DacAzureDatabaseSpecification();
dbSpec.Edition = edition;
dacServiceInstance.ImportBacpac(dacpac, importDBName);
dacpac.Dispose();
}
}
public override void Execute()
{
BacPackage bacpac = BacPackage.Load(this.Parameters.PackageFilePath);
this.DacServices.ImportBacpac(bacpac, this.Parameters.DatabaseName, this.CancellationToken);
}
private static async Task CloneDatabase(string[] args, CancellationToken cancellation)
{
#region Arguments
// Build the configuration root based on project user secrets
IConfiguration config = new ConfigurationBuilder()
.AddUserSecrets(typeof(Program).Assembly)
.AddCommandLine(args) // Higher precedence
.Build();
var opt = config.Get <ClonerOptions>();
if (string.IsNullOrWhiteSpace(opt.ConnectionString))
{
throw new ArgumentException($"The parameter {nameof(opt.ConnectionString)} is required.");
}
while (string.IsNullOrWhiteSpace(opt.Source))
{
Write("Enter Source DB Name: ");
opt.Source = ReadLine();
WriteLine();
}
if (string.IsNullOrWhiteSpace(opt.Destination))
{
throw new ArgumentException($"The parameter {nameof(opt.Destination)} is required.");
}
#endregion
#region Connection Strings
string neutralConnString;
string sourceConnString;
string destinationConnString;
{
var bldr = new SqlConnectionStringBuilder(opt.ConnectionString)
{
InitialCatalog = ""
};
neutralConnString = bldr.ConnectionString;
// Source
bldr.InitialCatalog = opt.Source;
sourceConnString = bldr.ConnectionString;
// Destination
bldr.InitialCatalog = opt.Destination;
destinationConnString = bldr.ConnectionString;
}
#endregion
// Initial setup
var service = new DacServices(neutralConnString);
service.Message += (object s, DacMessageEventArgs e) =>
{
WriteLine(e.Message.Message);
};
string bacpacPath = $"{Guid.NewGuid():N}.bacpac";
try
{
// Exporting package from source
{
WriteLine();
WriteLine($"============= Exporting from {opt.Source}...");
service.ExportBacpac(bacpacPath, opt.Source, null, cancellation);
WriteLine($"\u2713 Exporting {opt.Source} is complete.", ConsoleColor.Green);
WriteLine();
}
// Import package in destination
{
WriteLine($"============= Importing into {opt.Destination}...");
var package = BacPackage.Load(bacpacPath);
service.ImportBacpac(package, opt.Destination, new DacAzureDatabaseSpecification {
Edition = DacAzureEdition.Basic
}, cancellation);
WriteLine($"\u2713 Importing into {opt.Destination} is complete.", ConsoleColor.Green);
}
// Changing the navbar color
{
using var conn = new SqlConnection(destinationConnString);
using var cmd = conn.CreateCommand();
cmd.CommandText = @$ "UPDATE [dbo].[Settings] SET [BrandColor] = N'#5c5c5c', [SettingsVersion] = NEWID();";
await conn.OpenAsync(cancellation);
await cmd.ExecuteNonQueryAsync(cancellation);
WriteLine($"\u2713 Updated {opt.Destination} brand color to gray.", ConsoleColor.Green);
WriteLine();
}
}
finally
{
File.Delete(bacpacPath);
}
#region Launch Chrome
if (!string.IsNullOrWhiteSpace(opt.LaunchUrl))
{
Process process = new();
process.StartInfo.UseShellExecute = true;
process.StartInfo.FileName = "chrome";
process.StartInfo.Arguments = opt.LaunchUrl;
process.Start();
}
#endregion
}
