public static void ApplyDacpac(Stream dacpac, string connectionString, string databaseName, ILogger log)
{
var options = new DacDeployOptions()
{
BlockOnPossibleDataLoss = true,
IncludeTransactionalScripts = true,
DropConstraintsNotInSource = false,
DropIndexesNotInSource = false,
DropDmlTriggersNotInSource = false,
DropObjectsNotInSource = false,
DropExtendedPropertiesNotInSource = false,
DropPermissionsNotInSource = false,
DropStatisticsNotInSource = false,
DropRoleMembersNotInSource = false,
};
var service = new DacServices(connectionString);
service.Message += (x, y) =>
{
log.Log(y.Message.Message);
};
try
{
using (var package = DacPackage.Load(dacpac))
{
service.Deploy(package, databaseName, true, options);
}
}
catch (Exception e)
{
log.Log(e.Message, true);
}
}
public void ExtractDacpac(string filePath, IEnumerable<Tuple<string, string>> tables = null, DacExtractOptions extractOptions = null)
{
DacServices ds = new DacServices(this.BuildConnectionString());
ds.Extract(filePath, this.DatabaseName, this.DatabaseName, new Version(1, 0, 0), string.Empty, tables, extractOptions);
}
public DacServicesUtil(string servername)
{
sb.IntegratedSecurity = true;
sb.DataSource = servername;
sb.Pooling = false;
ds = new DacServices(sb.ConnectionString);
}
public static void Main(string[] args)
{
try
{
var appEnv =
CallContextServiceLocator.Locator.ServiceProvider.GetService(typeof (IApplicationEnvironment)) as
IApplicationEnvironment;
if (appEnv == null) throw new Exception("Couldn't get application environment to get base path.");
var dacPackageLocation = Path.Combine(appEnv.ApplicationBasePath, "database.dacpac");
var dacPackage = DacPackage.Load(dacPackageLocation);
var dacServices = new DacServices(ConnectionString);
dacServices.Deploy(dacPackage, "DOESNTMATTER", true, new DacDeployOptions
{
GenerateSmartDefaults = true,
CreateNewDatabase = true
});
}
catch (Exception ex)
{
// Normally, you should get a "connection can't be made exception", becase the connection string is garbage.
// This is expected and normal.
// However, we get a "resource" not found exception. This exception goes away when we move
// "Microsoft.Data.Tools.Schema.Sql.dll" and "Microsoft.SqlServer.Dac.dll" into the directory
// where "dnx.exe" is located.
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Clone an existing database schema and deploy.
/// </summary>
/// <param name="sourceDatabase">Source database connection string.</param>
/// <param name="databaseName">Database name.</param>
/// <exception cref="ArgumentException">
/// <paramref name="sourceDatabase"/> is <c>null</c> or <see cref="string.Empty"/>
/// or
/// <paramref name="databaseName"/> is <c>null</c> or <see cref="string.Empty"/>
/// </exception>
/// <returns>Database connection string.</returns>
public string CloneDatabase(string sourceDatabase, string databaseName)
{
if (string.IsNullOrEmpty(sourceDatabase))
{
throw new ArgumentException("The source database cannot be null or empty.", nameof(sourceDatabase));
}
if (string.IsNullOrEmpty(databaseName))
{
throw new ArgumentException("The database name cannot be null or empty.", nameof(databaseName));
}
lock (_sync)
{
using (var stream = new MemoryStream())
{
var extractService = new DacServices(sourceDatabase);
extractService.Extract(stream, databaseName, "SqlServer.Test",
Version.Parse("0.0.1"), "SqlServer.Test", null, DacpacOptions.Extract);
DacPackage.Deploy(stream, _serverConnectionString, databaseName, Settings);
}
_databases.Add(databaseName);
return(CreateDatabaseConnectionString(databaseName));
}
}
/// <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);
}
}
/// <summary>
/// Loads database from file.
/// </summary>
public void LoadDataBase()
{
var dacOptions = new DacDeployOptions {
CreateNewDatabase = true
};
var dacServiceInstance = new DacServices(_connectionString);
var dacpacPath = ConfigurationManager.AppSettings["dacpacFilePath"];
if (dacpacPath != null && dacpacPath.Contains("AppPath"))
{
dacpacPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + dacpacPath.Replace("AppPath", string.Empty);
}
if (File.Exists(dacpacPath))
{
using (DacPackage dacpac = DacPackage.Load(dacpacPath))
{
dacServiceInstance.Deploy(dacpac, "TicketManagement", true, dacOptions);
}
}
else
{
throw new ConfigurationErrorsException("Error load database from dacpac file.");
}
}
private static void Import(Options options)
{
var package = Microsoft.SqlServer.Dac.BacPackage.Load(options.BacpacFile);
var service = new DacServices(options.ConnectionStringBuilder().ConnectionString);
service.ProgressChanged += ServiceOnProgressChanged;
service.ImportBacpac(package, options.ConnectionStringBuilder().InitialCatalog);
}
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);
}
/// <summary>
/// Initializes a new instance of the <see cref="DatabaseDeploymentService"/> class.
/// </summary>
/// <param name="targetConnectionString">Connection string to the database upon which the database deployment service is to operate.</param>
public DatabaseDeploymentService(string targetConnectionString)
{
if (string.IsNullOrWhiteSpace(targetConnectionString))
{
throw new ArgumentException("targetConnectionString cannot be null or empty string.", "targetConnectionString");
}
this.dacService = new DacServices(targetConnectionString);
}
static void Main(string[] args)
{
DacServices ds = new DacServices(@"Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=true");
using (DacPackage dp = DacPackage.Load(@"C:\temp\mydb.dacpac"))
{
ds.Deploy(dp, @"DATABASENAME", upgradeExisting: false, options: null, cancellationToken: null);
}
}
private static string MainExec(string sourceDacFilePath, string sourceConnectionString, string targerConnectionString, string username, string password, DacDeployOptions options = null, CancellationTokenSource C_Token = null)
{
using (var impersonator = new ImpersonateIt())
{
impersonator.Impersonate(username, password);
//if (!System.IO.File.Exists(sourceDacFilePath))
//{
// Console.WriteLine("source dac file does not exists, Creating new file. ");
// if (string.IsNullOrWhiteSpace(sourceConnectionString))
// {
// Console.Error.WriteLine("Source Connection string is required for creating a bac file.");
// return string.Empty;
// }
//}
Export(sourceConnectionString, @"C:\Temp\Source_dacFile.dacpac");
Export(targerConnectionString, @"C:\Temp\Target_dacFile.dacpac");
var TargetCon = new SqlConnectionStringBuilder(targerConnectionString);
var TargetdacServices = new DacServices(TargetCon.ConnectionString);
TargetdacServices.Message += ((s, e) => { Console.WriteLine(e?.Message.ToString()); });
TargetdacServices.ProgressChanged += ((s, e) => { Console.WriteLine("Status:{0}, Message:{1}", e?.Status, e?.Message.ToString()); });
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(sourceDacFilePath, DacSchemaModelStorageType.Memory))
{
// Script then deploy, to support debugging of the generated plan
// string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
var deployReport = TargetdacServices.GenerateDeployReport(dacpac, TargetCon.InitialCatalog);
var deployScript = TargetdacServices.GenerateDeployScript(dacpac, TargetCon.InitialCatalog);
var DiffReport = TargetdacServices.GenerateDriftReport(TargetCon.InitialCatalog);
var outReportPath = Path.Combine(@"C:\Temp\", "DeployReport_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outReportPath, deployReport);
var outScriptPath = Path.Combine(@"C:\Temp\", "DeployScript_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outScriptPath, deployScript);
var outDiffReport = Path.Combine(@"C:\Temp\", "DeployDiff_" + DateTime.Now.ToString("yyyyMMMdd HHmmsstt") + ".sql");
System.IO.File.WriteAllText(outDiffReport, DiffReport);
Console.WriteLine("output Report and script generated.");
Console.WriteLine("DeployReport.{0}", deployReport);
Console.WriteLine("DiffReport.{0}", DiffReport);
Console.WriteLine("DeployScript.{0}", deployScript);
return("Done.");
}
}
return("");
}
public void RunAnalysisAgainstDatabase(string Server,string Database,string OutFile)
{
string extractedPackagePath = System.IO.Path.GetTempPath()+System.IO.Path.GetRandomFileName() + ".dacpac";
DacServices services = new DacServices("Server="+Server+";Integrated Security=true;");
services.Extract(extractedPackagePath, Database, "AppName", new Version(1, 0));
RunDacpacAnalysis(extractedPackagePath,OutFile);
}
private PackageOptions _unusedOptions = new PackageOptions(); //without this the Microsoft.SqlServer.Dac.Extensions.dll is not included in the build
public Proxy(string dacPacPath, string profilePath)
{
Profile = DacProfile.Load(profilePath);
Services = new DacServices(Profile.TargetConnectionString);
Services.Message += (sender, args) => Message.Invoke(sender, $"{args.Message.Prefix}, {args.Message.MessageType}, {args.Message.Number}, {args.Message.Message}");
Services.ProgressChanged += (sender, args) => Message.Invoke(sender, $"{args.Message}, {args.Status.ToString().Replace("Completed", "Complete")}");
DacpacPath = dacPacPath;
}
public void DeployDacpac(string dacpacFileName, string dbName, string connectionstring)
{
dacpacFileName = Path.GetFullPath(dacpacFileName);
var dacServices = new DacServices(connectionstring);
dacServices.Message += (sender, args) => Debug.WriteLineIf(Debugger.IsAttached, args.Message);
dacServices.ProgressChanged += OnDacServerProcessChanged;
var package = DacPackage.Load(dacpacFileName);
CancellationToken? cancellationToken = new CancellationToken();
dacServices.Deploy(package, dbName, true, null, cancellationToken);
}
/// <summary>
/// Set up a database for a given <see cref="SqlConnection"/> using a DACPAC.
/// </summary>
/// <param name="connectionString">The sql connection string for the server.</param>
/// <param name="targetDatabaseName">The target database name for the server.</param>
/// <param name="dacpacPath">The path to the DACPAC.</param>
public static void SetupDatabaseFromDacPac(string connectionString, string targetDatabaseName, string dacpacPath)
{
var services = new DacServices(connectionString);
using var package = DacPackage.Load(dacpacPath);
services.Publish(package, targetDatabaseName, new PublishOptions {
DeployOptions = new DacDeployOptions {
CreateNewDatabase = true
}, GenerateDeploymentReport = false
});
}
public static void DeployDacpac(string testDatabase, string databaseName)
{
var dacpacFile = string.Format(TestRunnerHelper.DacpacLocation, testDatabase);
using (var package = DacPackage.Load(dacpacFile, DacSchemaModelStorageType.Memory, FileAccess.Read))
{
var service = new DacServices(ConnectionString(databaseName));
service.Deploy(package, databaseName);
}
}
private DacServices GetTargetDacServices()
{
SqlConnectionStringBuilder targetBuilder = new SqlConnectionStringBuilder
{
DataSource = TargetServerName,
InitialCatalog = TargetDatabaseName,
IntegratedSecurity = true
};
DacServices targetServices = new DacServices(targetBuilder.ConnectionString);
return(targetServices);
}
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);
}
/// <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 void Create(string targetDatabaseName)
{
var dacpac = GetDacPackage();
var service = new DacServices(_connBuilder.ConnectionString);
var options = new DacDeployOptions
{
IncludeTransactionalScripts = true,
BlockOnPossibleDataLoss = false,
};
service.Message += service_Message;
service.ProgressChanged += service_ProgressChanged;
service.Deploy(dacpac, targetDatabaseName, true, options);
}
public static void TestClassInitialize(TestContext context)
{
// Generate a name for the temporary database
testDatabaseName = string.Format(CultureInfo.InvariantCulture, "TestRun_{0}", DateTime.Now.ToString("s", CultureInfo.InvariantCulture).Replace(':', '_'));
// Use DAC service to deploy the temporary database
var dacService = new DacServices(string.Format(CultureInfo.InvariantCulture, ConnectionStringTemplate, "master"));
var dacPacLocation = Path.Combine(
// ReSharper disable once AssignNullToNotNullAttribute
Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location),
"Snek.ToolsSample.Database.dacpac");
using (var stream = File.OpenRead(dacPacLocation))
{
using (var package = DacPackage.Load(stream))
{
dacService.Deploy(package, testDatabaseName);
}
}
}
public async Task Export()
{
var services = new DacServices(_connectionString);
services.ProgressChanged += ProgressChangedOnMessage;
string blobName;
if (string.IsNullOrEmpty(_filePath))
{
_filePath = Path.GetTempFileName();
_deleteFile = true;
blobName = string.Format("{0:yyyyMMddTHHmmss}-{1}.bacpac", DateTimeOffset.UtcNow, _databaseName);
}
else
{
blobName = Path.GetFileName(_filePath);
}
try
{
using (FileStream stream = File.Open(_filePath, FileMode.Create, FileAccess.ReadWrite))
{
Console.WriteLine("starting bacpac export");
await services.ExportBacpacAsync(stream, _databaseName, _token);
await stream.FlushAsync();
stream.Seek(0, SeekOrigin.Begin);
if (_container != null && !_token.IsCancellationRequested)
{
Console.WriteLine("uploading to azure blob storage");
CloudBlockBlob blob = _container.GetBlockBlobReference(blobName);
await blob.UploadFromStreamAsync(stream, _token);
}
}
}
finally
{
Cleanup();
}
}
protected DacServices ConnectDac(SqlConnectionInfo connInfo)
{
// We have to use an insecure string :(
var connStr = new SqlConnectionStringBuilder(connInfo.ConnectionString.ConnectionString)
{
InitialCatalog = "master"
};
connStr.UserID = connInfo.Credential.UserId;
var passwordPtr = Marshal.SecureStringToBSTR(connInfo.Credential.Password);
connStr.Password = Marshal.PtrToStringBSTR(passwordPtr);
Marshal.FreeBSTR(passwordPtr);
var services = new DacServices(connStr.ConnectionString);
services.Message += (s, a) =>
{
Task t = null;
switch (a.Message.MessageType)
{
case DacMessageType.Error:
t = Console.WriteErrorLine(a.Message.Message);
break;
case DacMessageType.Message:
t = Console.WriteInfoLine(a.Message.Message);
break;
case DacMessageType.Warning:
t = Console.WriteWarningLine(a.Message.Message);
break;
}
if (t != null)
{
t.Wait();
}
};
return services;
}
private void Import(DacServices svc, string TargetDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Import of {0} to {1} at {2}", Path, TargetDatabaseName, System.DateTime.Now.ToLongTimeString());
using (BacPackage bacpac = BacPackage.Load(Path))
{
svc.ImportBacpac(bacpac, TargetDatabaseName);
}
}
private DacServices GetService(string server)
{
DacServices svc = new DacServices(server);
svc.Message += new EventHandler<DacMessageEventArgs>(receiveDacServiceMessageEvent);
svc.ProgressChanged += new EventHandler<DacProgressEventArgs>(receiveDacServiceProgessEvent);
return svc;
}
private void Extract(DacServices svc, string SourceDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Extract of {0} to {1} at {2}", SourceDatabaseName, Path, System.DateTime.Now.ToLongTimeString());
DacExtractOptions dacExtractOptions = new DacExtractOptions
{
ExtractApplicationScopedObjectsOnly = true,
ExtractReferencedServerScopedElements = false,
VerifyExtraction = true,
Storage = DacSchemaModelStorageType.Memory
};
svc.Extract(Path, SourceDatabaseName, "Sample DACPAC", new Version(1, 0, 0), "Sample Extract", null, dacExtractOptions);
}
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);
}
/// <summary>
/// Import a bacpac file into a database
/// </summary>
/// <param name="targetConnString">connection string of target database</param>
/// <param name="targetDatabaseName">target database name</param>
/// <param name="bacpacPath">bacpac path file</param>
/// <returns>success status of opertaion</returns>
public static bool ImportBacpac(string targetConnString, string targetDatabaseName, string bacpacPath)
{
try
{
dacService = new DacServices(targetConnString);
dacService.Message += new EventHandler<DacMessageEventArgs>(DacMessageEvent);
dacService.ProgressChanged += new EventHandler<DacProgressEventArgs>(DacProgressEvent);
Console.WriteLine(" Importing {0} to {1} ", bacpacPath, targetDatabaseName);
using (var bacpac = BacPackage.Load(bacpacPath))
{
dacService.ImportBacpac(bacpac, targetDatabaseName);
}
Console.WriteLine(" Done Importing {0} ", targetDatabaseName);
return true;
}
catch (Exception ex)
{
Console.WriteLine(" " + ex.GetType() + ": " + ex.Message);
return false;
}
}
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 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 void TestIncludePlanFiltererInDacpac()
{
// Given a model with objects that use "dev", "test" and "prod" schemas
// and the contributor information built in
var model = CreateTestModel();
string existingPackagePath = GetTestFilePath("includesContributor.dacpac");
Console.WriteLine("Build dacpac to \n" + existingPackagePath);
DacPackageExtensions.BuildPackage(existingPackagePath, model, new PackageMetadata(), new PackageOptions()
{
DeploymentContributors = new[] { new DeploymentContributorInformation() { ExtensionId = PlanFilterer.PlanFiltererContributorId } }
});
DacServices services = new DacServices("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;");
// When publishing to production (filtering to exclude "dev" and "test" schemas)
string productionDbName = "ProductionDB";
using (DacPackage package = DacPackage.Load(existingPackagePath, DacSchemaModelStorageType.Memory))
{
DacDeployOptions options = new DacDeployOptions();
// Specify the filter to use and what arguments it needs. Note that this is a little limited by
// having to pass string-based arguments. This could be worked around by serializing arguments to a
// file and passing the file path to the contributor if you need to do anything advanced.
options.AdditionalDeploymentContributorArguments =
PlanFilterer.BuildPlanFiltererArgumentString("SchemaBasedFilter", new Dictionary<string, string>()
{
{"Schema1", "dev"},
{"Schema2", "test"},
});
// For test purposes, always create a new database (otherwise previous failures might mess up our result)
options.CreateNewDatabase = true;
// Run the deployment with the options as specified
services.Deploy(package, productionDbName, upgradeExisting: true, options: options);
}
// Then expect only the "prod" schema objects to remain in the new package
// Extract the dacpac back from the database and ensure that only production elements are there
string extractedPackagePath = GetTestFilePath("extracted.dacpac");
services.Extract(extractedPackagePath, productionDbName, "AppName", new Version(1, 0));
var extractedModel = _trash.Add(new TSqlModel(extractedPackagePath, DacSchemaModelStorageType.Memory));
Assert.AreEqual(TopLevelProdElementCount, CountTablesViewsAndSchemas(extractedModel));
AssertAllObjectsHaveSchemaName(extractedModel, "prod");
}
public void DeployDacPac(string databaseName)
{
ProviderConfiguration config =
(ProviderConfiguration)ConfigurationManager.GetSection("dbTestMonkey/" + ConfigurationSectionName);
SqlDatabaseConfiguration databaseConfiguration = config.Databases[databaseName];
string dacpacPath = databaseConfiguration.DacPacFilePath;
_logAction("Loading Dacpac into memory");
Stopwatch totalTimer = Stopwatch.StartNew();
Stopwatch loadPackageTimer = Stopwatch.StartNew();
_logAction("current directory:" + Environment.CurrentDirectory);
_logAction("dacpacPath:" + dacpacPath);
using (DacPackage dacPackage = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory, FileAccess.Read))
{
databaseName = databaseName ?? dacPackage.Name;
loadPackageTimer.Stop();
_logAction("Package loaded, initialising DacServices");
using (IDbConnection connection = _connectionFactory())
{
try
{
connection.ChangeDatabase(databaseName);
}
catch
{
_logAction(
"Could not change connection to database " +
databaseName +
" before pre-deployment script. Database may not yet exist.");
}
// Execute the DAC pre-deployment script.
if (dacPackage.PreDeploymentScript != null)
{
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = new StreamReader(dacPackage.PreDeploymentScript).ReadToEnd();
command.CommandText = command.CommandText.Replace("\nGO", "");
command.ExecuteNonQuery();
}
}
_logAction("Deploying dacpac");
Stopwatch dacpacDeployTimer = Stopwatch.StartNew();
DacDeployOptions options = new DacDeployOptions()
{
CreateNewDatabase = true
};
Stopwatch dacpacServiceTimer = Stopwatch.StartNew();
DacServices dacServices = new DacServices(connection.ConnectionString);
dacpacServiceTimer.Stop();
_logAction("DacServices initialisation took " + dacpacServiceTimer.ElapsedMilliseconds + " ms");
dacServices.Message += dacServices_Message;
dacServices.ProgressChanged += dacServices_ProgressChanged;
dacServices.Deploy(dacPackage, databaseName, upgradeExisting: true, options: options);
dacpacDeployTimer.Stop();
_logAction(
"Deploying dacpac took " +
dacpacDeployTimer.ElapsedMilliseconds +
" ms");
// If the user has opted to only run the post-deployment script after the DACPAC
// deployment and not per-test, it needs to run once.
if (!config.Databases[databaseName].ExecutePostDeploymentScriptPerTest)
{
ExecutePostDeploymentScript(databaseName, dacPackage);
}
}
}
totalTimer.Stop();
_logAction("Total dacpac time was " + totalTimer.ElapsedMilliseconds + " ms");
}
public void TestStopDeployment()
{
// Given database name
string dbName = TestContext.TestName;
// Delete any existing artifacts from a previous run
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
// When deploying using the deployment stopping contributor
try
{
DacDeployOptions options = new DacDeployOptions
{
AdditionalDeploymentContributors = DeploymentStoppingContributor.ContributorId
};
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
// Script then deploy, to support debugging of the generated plan
try
{
dacServices.GenerateDeployScript(dacpac, dbName, options);
Assert.Fail("Expected Deployment to fail and exception to be thrown");
}
catch (DacServicesException expectedException)
{
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaPublishMessage),
"Expected Severity.Error message passed to base.PublishMessage to block deployment");
Assert.IsTrue(expectedException.Message.Contains(DeploymentStoppingContributor.ErrorViaThrownException),
"Expected thrown exception to block deployment");
}
}
// Also expect the deployment to fail
AssertDeployFailed(TestUtils.ServerConnectionString, dbName);
}
finally
{
TestUtils.DropDatabase(TestUtils.ServerConnectionString, dbName);
}
}
private string GenerateScriptAndOptionallyDeploy(string dbName, DacDeployOptions options, bool runDeployment, int currentIteration)
{
if (options == null)
{
options = new DacDeployOptions();
}
using (DacPackage dacpac = DacPackage.Load(_dacpacPath, DacSchemaModelStorageType.Memory))
{
DacServices dacServices = new DacServices(TestUtils.ServerConnectionString);
// Script then deploy, to support debugging of the generated plan
string script = dacServices.GenerateDeployScript(dacpac, dbName, options);
string filePath = GetTestFilePath(string.Format(CultureInfo.CurrentCulture, "deployscript{0}.sql", currentIteration));
File.WriteAllText(filePath, script);
Console.WriteLine("Deployment script written to {0}", filePath);
if (runDeployment)
{
dacServices.Deploy(dacpac, dbName, true, options);
AssertDeploySucceeded(TestUtils.ServerConnectionString, dbName);
}
return script;
}
}
private static void PublishProductionDacpacAndVerifyContents(string productionPackagePath)
{
string extractedPackagePath = GetFilePathInCurrentDirectory("extracted.dacpac");
using (DacPackage package = DacPackage.Load(productionPackagePath, DacSchemaModelStorageType.Memory))
{
Console.WriteLine("Deploying the production dacpac to 'ProductionDB'");
DacServices services = new DacServices("Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;");
services.Deploy(package, "ProductionDB");
Console.WriteLine("Extracting the 'ProductionDB' back to a dacpac for comparison");
services.Extract(extractedPackagePath, "ProductionDB", "AppName", new Version(1, 0));
}
using (TSqlModel extractedModel = new TSqlModel(extractedPackagePath, DacSchemaModelStorageType.Memory))
{
Console.WriteLine("Objects found in extracted package: '" + productionPackagePath + "'");
PrintTablesViewsAndSchemas(extractedModel);
}
}
// source server
// destination server
// source db
// destination db
// filename
public void Start()
{
DacServices svc = new DacServices(SourceServer);
String filePath = getDACFilePath();
switch (OperationType)
{
case DeploySQLDAC.OperationType.GenerateDACPAC:
Extract(GetService(SourceServer), SourceDB, filePath);
break;
case DeploySQLDAC.OperationType.DeployDACPAC:
Extract(GetService(SourceServer), SourceDB, filePath);
Deploy(GetService(TargetServer), TargetDB, filePath);
break;
case DeploySQLDAC.OperationType.GenerateBACPAC:
Export(GetService(SourceServer), SourceDB, filePath);
break;
case DeploySQLDAC.OperationType.DeployBACPAC:
Export(GetService(SourceServer), SourceDB, filePath);
Import(GetService(TargetServer), TargetDB, filePath);
break;
case DeploySQLDAC.OperationType.DeployDACFromFile:
Deploy(GetService(TargetServer), TargetDB, this.FilePath);
break;
case DeploySQLDAC.OperationType.DeployBACFromFile:
Import(GetService(TargetServer), TargetDB, this.FilePath);
break;
}
}
public static SqlTestDB CreateFromDacpac(InstanceInfo instance, string dacpacPath, DacDeployOptions deployOptions = null, bool dropDatabaseOnCleanup = false)
{
string dbName = Path.GetFileNameWithoutExtension(dacpacPath);
DacServices ds = new DacServices(instance.BuildConnectionString(dbName));
using (DacPackage dp = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
ds.Deploy(dp, dbName, true, deployOptions);
}
var sqlDb = new SqlTestDB(instance, dbName, dropDatabaseOnCleanup);
return sqlDb;
}
private void Deploy(DacServices svc, string TargetDatabaseName, string Path)
{
Console.WriteLine("\n\rPerforming Deploy of {0} to {1} at {2}", Path, TargetDatabaseName, System.DateTime.Now.ToLongTimeString());
using (DacPackage dacpac = DacPackage.Load(Path))
{
//svc.Deploy(dacpac, TargetDatabaseName);
svc.Deploy(dacpac, TargetDatabaseName, true);
}
}
public void Deploy(string connectionString, string databaseName) {
var services = new DacServices(connectionString);
services.Deploy(_dacpac, databaseName, upgradeExisting: true);
}
/// <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;
}
}
