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);
}
}
}
private static void Export(Options options)
{
var service = new DacServices(options.ConnectionStringBuilder().ConnectionString);
service.ProgressChanged += ServiceOnProgressChanged;
service.ExportBacpac(options.BacpacFile, options.ConnectionStringBuilder().InitialCatalog);
}
private static void TimerTask(object timerState)
{
DateTime now = DateTime.Now;
var minute = now.Minute;
if (minute == 30 || minute == 0)
{
string data = now.ToString("dd-MM-yyyy-HH-mm-ss");
Console.WriteLine(data);
var connectionString = "Server=DESKTOP-08EQ49B; " +
"Database=bober; User Id=test; Password=123456; " +
"MultipleActiveResultSets=true";
var services = new DacServices(connectionString);
services.Message += (sender, e) =>
{
// If you use a lock file,
// this would be a good location to extend the lease
};
services.ExportBacpac("D://" + data + ".bacpac", "bober");
var state = timerState as TimerState;
Interlocked.Increment(ref state.Counter);
}
}
public async Task Export(ProgressDialogController progress)
{
if (!Exists)
{
return;
}
await Task.Run(() =>
{
// export bacpac
var bacpacName = $"{DateTime.Now:yyyyMMdd_HHmm}.bacpac";
_dacService.ProgressChanged += (sender, args) => progress.SetMessage($"{args.Status} : {args.Message}");
_dacService.ExportBacpac(bacpacName, _databaseName);
// save file
var filePath = Path.Combine(Directory.GetCurrentDirectory(), bacpacName);
var result = new SaveFileDialogService().Show(
bacpacName
, fileName =>
{
if (!File.Exists(fileName))
{
File.Move(filePath, fileName);
}
}
, () => File.Delete(filePath)
);
progress.SetMessage(result ? "Success" : "Fail");
});
await progress.CloseAsync();
}
public Task ExportAsync(string sourceServerName, string sourceDatabaseName, string targetBacPacFilePath, CancellationToken cancellationToken)
{
var connectionString = $@"Data Source={sourceServerName};Integrated Security=True";
var services = new DacServices(connectionString);
services.Message += (_, e) => LogMessage(e.Message?.Message);
services.ExportBacpac(targetBacPacFilePath, sourceDatabaseName, cancellationToken: cancellationToken);
return(Task.CompletedTask);
}
private static void exportBacpac(string database, string databaseUser, string databasePassword, string address)
{
DacServices svc = new DacServices("Data Source=" + address + ";Initial Catalog=" + database + ";User ID=" + databaseUser + ";Password="******"dump.bacpac", database);
}
public static void CreateBacpacFile(ICakeContext context, string connectionString, string databaseName, string resultingFilePath)
{
context.Log.Information($"About to create a bacpac file from database {databaseName}");
var service = new DacServices(connectionString);
service.ExportBacpac(resultingFilePath, databaseName);
context.Log.Information($"Finished creating bacpac file from database {databaseName}. File location is {resultingFilePath}");
}
/// <summary>
/// Creates a bacpac file of the device's database and returns the local path of that filename
/// </summary>
public static void CreateDatabaseBackup()
{
var tempPathForbacpac = Path.Combine(Path.GetTempPath(), "azureBackup");
Directory.CreateDirectory(tempPathForbacpac);
var filepath = Path.Combine(tempPathForbacpac, "data.bac");
//Trace.WriteLine("SQL Scanner - Azure database backup location: " + filepath, "Information");
var i = 0;
const int maxRetries = 3;
while (true)
{
i++;
try
{
if (File.Exists(filepath))
{
File.Delete(filepath);
}
var dacSvc = new DacServices(System.Configuration.ConfigurationManager.AppSettings["AzureInstallConnectionString"]);
//dacSvc.Message += (sender, e) => Trace.WriteLine(e.Message);
dacSvc.ExportBacpac(filepath, System.Configuration.ConfigurationManager.AppSettings["TestInstallDBName"]);
break;
}
catch (Exception ex)
{
if (i == maxRetries)
{
throw;
}
Trace.WriteLine(string.Format("Exception occurred while creating the database backup for file {0} (retry {1} of {2}: {3}", filepath, i, maxRetries, ex.Message));
Trace.TraceError(ex.Message);
Trace.TraceError(ex.StackTrace);
if (ex.InnerException != null)
{
Trace.TraceError(ex.InnerException.Message);
}
Thread.Sleep(i * 1000);
}
}
}
/// <summary>
/// Db backup procedure
/// </summary>
public void Backup()
{
string folder = Environment.GetEnvironmentVariable("TEMP"); // Windows temp folder
string fileName = DateTime.UtcNow.Ticks + backupContext.DatabaseName.ToLower() + ".bacpac";
try
{
var svc = new DacServices(backupContext.DatabaseConnection);
svc.Message += receiveDacServiceMessageEvent;
svc.ProgressChanged += receiveDacServiceProgessEvent;
Console.WriteLine("\n\rPerforming Export of {0} to {1} at {2}", backupContext.DatabaseName, fileName,
DateTime.Now.ToLongTimeString());
svc.ExportBacpac(folder + "/" + fileName, backupContext.DatabaseName);
//Push to storage
var storage = new StorageHandler(backupContext.StorageAccount);
storage.SetBlob(backupContext.StorageContainer, folder + "/" + fileName);
var f = new FileInfo(folder + "/" + fileName);
if (backupContext.MailSetting.SMTPServer != null && backupContext.MailSetting.MailReceiver != null)
{
//Change here for custom mail message
MailSender.SendMail(backupContext.MailSetting.MailReceiver,
backupContext.MailSetting.MailReceiver,
"Backup location: " + backupContext.StorageContainer + "/" + fileName +
"\n\n File Size: " + f.Length.ToString() + " bytes, Time (UTC): " +
DateTime.UtcNow,
"Backup generated for " + backupContext.DatabaseName,
backupContext.MailSetting.SMTPServer,
backupContext.MailSetting.SMTPServerPort,
backupContext.MailSetting.SMTPCredentials);
}
File.Delete(folder + "/" + fileName);
}
catch (Exception ex) // on error
{
if (backupContext.MailSetting.SMTPServer != null && backupContext.MailSetting.MailReceiver != null)
{
//Change here for custom mail message
MailSender.SendMail(backupContext.MailSetting.MailReceiver,
backupContext.MailSetting.MailReceiver,
"Backup location: " + backupContext.StorageContainer + "/" + fileName +
"\n\n Time (UTC): " + DateTime.UtcNow + "\n\n Exception: " + ex,
"Error Backup generated for " + backupContext.DatabaseName,
backupContext.MailSetting.SMTPServer,
backupContext.MailSetting.SMTPServerPort,
backupContext.MailSetting.SMTPCredentials);
}
}
}
public static void BackupBDTrigger([TimerTrigger("0 59 23 * * 0", RunOnStartup = true)] TimerInfo myTimer, TextWriter log)
{
try
{
CloudStorageAccount storageAccount = AzureStorageAccount.DefaultAccount;
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer blobContainer = blobClient.GetContainerReference(AzureStorageContainer.BackupsBD);
blobContainer.CreateIfNotExists();
BlobContainerPermissions permissions = blobContainer.GetPermissions();
permissions.PublicAccess = BlobContainerPublicAccessType.Container;
blobContainer.SetPermissions(permissions);
string storageName = $"backup_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.bacpac";
CloudBlockBlob backupFile = blobContainer.GetBlockBlobReference(storageName);
string cnn = ConfigurationManager.ConnectionStrings["Limbs"].ConnectionString;
string dbName = null;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(cnn);
if (builder != null && builder.ContainsKey("Database"))
{
dbName = builder["Database"] as string;
if (string.IsNullOrEmpty(dbName))
{
dbName = builder["Initial Catalog"] as string;
}
}
string tempFile = $"{Path.GetTempPath()}{backupFile.Name}";
DacServices services = new DacServices(cnn);
services.ExportBacpac(tempFile, dbName);
backupFile.UploadFromFile(tempFile);
log.WriteLine($"URL PRIMARIA BLOB: {backupFile.StorageUri.PrimaryUri}");
log.WriteLine($"URL PRIMARIA BLOB: {backupFile.StorageUri.SecondaryUri}");
File.Delete(tempFile);
}
catch (Exception ex)
{
log.WriteLine($"OCURRIO UN ERROR: {ex.Message}");
}
}
private static int DatabaseToFileSync(Db2fOptions opts)
{
if (string.IsNullOrEmpty(opts.WorkingDirectory))
{
opts.WorkingDirectory = Environment.CurrentDirectory;
}
var azureConnectionString = opts.InputConnectionString;
var db = opts.Database;
Console.WriteLine($"Fetching {db}...");
Console.WriteLine();
var dir = new FileInfo(opts.OutputFile).DirectoryName;
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var dac = new DacServices(azureConnectionString);
dac.ProgressChanged += (sender, eventArgs) => { Console.WriteLine($"[{db}] {eventArgs.Message}"); };
try
{
dac.ExportBacpac(opts.OutputFile, db, DacSchemaModelStorageType.File);
}
catch (DacServicesException dex)
{
if (dex.InnerException == null)
{
throw;
}
throw new DacServicesException(dex.InnerException.Message, dex);
}
Console.WriteLine($"[{db}] Export completed");
return(0);
}
static void Main(string[] args)
{
SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder()
{
DataSource = "mydb.database.windows.net",
InitialCatalog = "mydb001",
UserID = "UserID",
Password = "******"
};
DacDeployOptions deployOptions = new DacDeployOptions()
{
IgnoreWhitespace = true,
IncludeTransactionalScripts = true,
IgnoreSemicolonBetweenStatements = true
};
DacServices service = new DacServices(connectionStringBuilder.ConnectionString);
service.ExportBacpac("MyDacPack-002", "mydb001");
}
public void Export(string databaseName, string targetFilename)
{
DacServices dacServices = new DacServices(connectionString);
dacServices.ExportBacpac(targetFilename, databaseName);
}
public void ExportBacpac(string filePath, IEnumerable<Tuple<string, string>> tables = null, DacExportOptions extractOptions = null)
{
DacServices ds = new DacServices(this.BuildConnectionString());
ds.ExportBacpac(filePath, this.DatabaseName, extractOptions, tables);
}
public void Export(string databaseName, string targetFilename)
{
DacServices dacServices = new DacServices(connectionString);
dacServices.ExportBacpac(targetFilename, databaseName);
}
public void ExportBacpac(string filePath, IEnumerable <Tuple <string, string> > tables = null, DacExportOptions extractOptions = null)
{
DacServices ds = new DacServices(this.BuildConnectionString());
ds.ExportBacpac(filePath, this.DatabaseName, extractOptions, tables);
}
private static async Task PublishDatabases(string[] args, CancellationToken cancellation)
{
#region Arguments
PublisherOptions opt;
SqlConnectionStringBuilder adminConnBldr = null;
{
// Build the configuration root based on project user secrets
IConfiguration config = new ConfigurationBuilder()
.AddUserSecrets(typeof(Program).Assembly)
.AddCommandLine(args) // Higher precedence
.Build();
opt = config.Get <PublisherOptions>();
// DACPAC File (Required)
if (!opt.SkipPublish)
{
while (string.IsNullOrWhiteSpace(opt.DacpacFile))
{
Write("Enter path to DACPAC file: ");
opt.DacpacFile = ReadLine();
if (opt.DacpacFile == null)
{
throw new OperationCanceledException();
}
}
if (!opt.DacpacFile.EndsWith(".dacpac"))
{
throw new ArgumentException($"DACPAC file must have the \".dacpac\" extension");
}
else if (!File.Exists(opt.DacpacFile))
{
throw new ArgumentException($"No DACPAC found at \"{opt.DacpacFile}\"");
}
}
// Admin Connection (Required)
while (string.IsNullOrWhiteSpace(opt.AdminConnection))
{
Write("Enter the admin DB connection string: ");
opt.AdminConnection = ReadLine();
if (opt.AdminConnection == null)
{
throw new OperationCanceledException();
}
}
try
{
adminConnBldr = new SqlConnectionStringBuilder(opt.AdminConnection);
}
catch
{
throw new ArgumentException($"Invalid connection string \"{opt.AdminConnection}\"");
}
// Pre-Publish Script
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript) && !File.Exists(opt.PrePublishScript))
{
throw new ArgumentException($"No Pre-Publish script found at \"{opt.PrePublishScript}\"");
}
// Backup Folder
string workingDirectory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
opt.BackupFolder ??= Path.Combine(workingDirectory, "Backups");
// To avoid negative batch size
opt.BatchSize = Math.Max(opt.BatchSize, 1);
}
#endregion
#region Tenants
List <PublishWorkspace> workspaces;
{
var adminOpt = Options.Create(new AdminRepositoryOptions {
ConnectionString = adminConnBldr.ConnectionString
});
var logger = new NullLogger <AdminRepository>();
var repo = new AdminRepository(adminOpt, logger);
var ctx = new QueryContext(0);
WriteLine($"Loading tenants info from server \"{adminConnBldr.DataSource}\"...");
var databases = await repo.SqlDatabases
.Expand(nameof(SqlDatabase.Server))
.OrderBy(nameof(SqlDatabase.Id))
.ToListAsync(ctx, cancellation);
workspaces = databases.Select(db =>
{
var connInfo = AdminRepositoryConnectionResolver.ToConnectionInfo(
serverName: db.Server.ServerName,
dbName: db.DatabaseName,
userName: db.Server.UserName,
_: db.Server.PasswordKey,
adminConnBuilder: adminConnBldr);
var dbConnBldr = new SqlConnectionStringBuilder
{
DataSource = connInfo.ServerName,
InitialCatalog = connInfo.DatabaseName,
UserID = connInfo.UserName,
Password = connInfo.Password,
IntegratedSecurity = connInfo.IsWindowsAuth,
PersistSecurityInfo = false,
};
return(new PublishWorkspace
{
DbName = db.DatabaseName,
ConnectionString = dbConnBldr.ConnectionString
});
})
.ToList();
Write($"\u2713 ", ConsoleColor.Green);
Write($"Found DBs:");
WriteLine($" [{string.Join("], [", workspaces.Select(w => w.DbName))}]", ConsoleColor.Cyan);
}
#endregion
#region Confirmation
if (!opt.SkipConfirmation)
{
string confirmed = "Confirmed";
WriteLine();
Write($"Confirm going ahead with all tenant DBs by typing ");
Write($"\"{confirmed}\"");
Write($": ");
var answer = ReadLine();
if (answer == null)
{
throw new OperationCanceledException();
}
else if (answer.ToLower() != confirmed.ToLower())
{
Write($"X", ConsoleColor.Red);
Write($" Did not confirm");
WriteLine();
return;
}
else
{
Write($"\u2713 ", ConsoleColor.Green);
Write("Confirmation acquired.");
WriteLine();
}
}
#endregion
#region Backup Dir
DateTime now = DateTime.Now;
string backupsPath = null;
if (!opt.SkipBackup)
{
string nowString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss");
backupsPath = Path.Combine(opt.BackupFolder, nowString);
Directory.CreateDirectory(backupsPath);
Write($"\u2713 ", ConsoleColor.Green);
Write($"Backup directory created at ");
Write(backupsPath, ConsoleColor.Cyan);
Write($".");
WriteLine();
}
#endregion
#region PrePublish Script
string prePublishScript = null;
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
{
prePublishScript = await File.ReadAllTextAsync(opt.PrePublishScript, cancellation);
}
#endregion
DacPackage dacPackage = null;
try
{
#region DACPAC
// Get the DACPAC package
if (!opt.SkipPublish)
{
dacPackage = DacPackage.Load(opt.DacpacFile);
{
// Sanity check just in case
string expectedName = "Tellma.Database.Application";
if (dacPackage.Name != expectedName)
{
throw new ArgumentException($"The DACPAC file \"{opt.DacpacFile}\" does not have the name {expectedName}.");
}
Write($"\u2713 ", ConsoleColor.Green);
WriteLine($"DACPAC version {dacPackage.Version} loaded.");
}
}
#endregion
#region Backup and Publish
WriteLine();
WriteLine($"Operation started at {now:hh:mm:ss tt}...");
foreach (var workspace in workspaces)
{
workspace.Top = Console.CursorTop;
workspace.UpdateStatus("Getting ready");
WriteLine();
}
int skip = 0;
while (!cancellation.IsCancellationRequested)
{
var batch = workspaces.Skip(skip).Take(opt.BatchSize);
if (batch.Any())
{
await Task.WhenAll(batch.Select(async ws =>
{
try
{
#region DacService
var service = new DacServices(ws.ConnectionString);
service.Message += (object s, DacMessageEventArgs e) =>
{
ws.UpdateStatus(e.Message);
};
#endregion
#region Backup
if (!opt.SkipBackup)
{
// Export Package
string bacpacPath = Path.Combine(backupsPath, $"{ws.DbName}.bacpac");
await Task.Run(() => service.ExportBacpac(bacpacPath, ws.DbName, null, cancellation), cancellation);
}
#endregion
#region Pre-Publish Script
if (!string.IsNullOrWhiteSpace(opt.PrePublishScript))
{
ws.UpdateStatus("Executing Pre-Publish Script (Started)");
using var conn = new Microsoft.Data.SqlClient.SqlConnection(ws.ConnectionString);
await Task.Run(() =>
{
Server server = new(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(prePublishScript);
});
ws.UpdateStatus("Executing Pre-Publish Script (Completed)");
}
#endregion
if (!opt.SkipPublish)
{
#region DB Specs
DacAzureDatabaseSpecification specs = null;
{
ws.UpdateStatus("Retrieving DB Specs (Started)");
using var conn = new SqlConnection(ws.ConnectionString);
using var cmd = conn.CreateCommand();
cmd.CommandText = @$ "
IF OBJECT_ID(N'sys.database_service_objectives') IS NOT NULL
SELECT
[edition] AS [Edition],
private static void Export(Options options)
{
var service = new DacServices(options.ConnectionStringBuilder().ConnectionString);
service.ProgressChanged += ServiceOnProgressChanged;
service.ExportBacpac(options.BacpacFile, options.ConnectionStringBuilder().InitialCatalog);
}
/// <summary>
/// Export bacpac file from a local dbase
/// </summary>
/// <param name="targetConnString">connection string of target database</param>
/// <param name="sourceDatabaseName"></param>
// <param name="bacpacPath">bacpac path file</param>
/// <returns>success status of opertaion</returns>
public static bool ExportLocalBacpac( string targetConnString, string sourceDatabaseName , string bacpacPath)
{
try
{
dacService = new DacServices(targetConnString);
dacService.Message += new EventHandler<DacMessageEventArgs>(DacMessageEvent);
dacService.ProgressChanged += new EventHandler<DacProgressEventArgs>(DacProgressEvent);
Console.WriteLine(" Exporting {0} to {1}", sourceDatabaseName, bacpacPath);
dacService.ExportBacpac(bacpacPath, sourceDatabaseName);
Console.WriteLine(" Done Exporting {0} to {1}", sourceDatabaseName, bacpacPath);
return true;
}
catch (Exception ex)
{
Console.WriteLine(" " + ex.GetType() + ": " + ex.Message);
return false;
}
}
private void Export(DacServices svc, string SourceDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Export of {0} to {1} at {2}", SourceDatabaseName, Path, System.DateTime.Now.ToLongTimeString());
svc.ExportBacpac(Path, SourceDatabaseName);
}
static void BACPAC作る()
{
var dac = new DacServices(connstr);
dac.ExportBacpac("d:/dotnetconf2019.bacpac", "dotnetconf2019");
}
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
}
