CODEWITHSUNDEEP
c#sql-server-data-toolsdacpacschema-comparesqlpackage
Fish
Fish

Reputation: 138

SSDT How can I create a new database from a DACPAC file?

Currently using SqlPackage.exe cmd line tools to generate a script, by comparing DacPac file with a target database. This will Create the database if it doesn't already exist.

We now want to switch this to use the Microsoft.Data.Tools.Msbuild Nuget package (using C# to deploy), but using SchemaComparison to Compare will fail when the database container does not exist. There doesn't seem to be much documentation related to the package.

Are there any tools as part of this package which will allow me to create an empty database container prior to comparing?

I'm currently using the following code (which works when comparing against an existing database):

 SchemaCompareDacpacEndpoint sourceDacpac = new SchemaCompareDacpacEndpoint(@"C:\DeployDB.dacpac");

        SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
        csb.DataSource = @".";
        csb.InitialCatalog = "DeployTestDB";
        csb.IntegratedSecurity = true;
        SchemaCompareDatabaseEndpoint targetDatabase = new SchemaCompareDatabaseEndpoint(csb.ToString());


        SchemaComparison comparison = new SchemaComparison(sourceDacpac, targetDatabase);
        comparison.Options.DropObjectsNotInSource = true;
        comparison.Options.BlockOnPossibleDataLoss = false;
        comparison.Options.TreatVerificationErrorsAsWarnings = true;
        comparison.Options.ScriptDatabaseOptions = true;
        comparison.Options.GenerateSmartDefaults = true;

        SchemaComparisonResult comparisonResult = comparison.Compare();

at this point, the ComparisonResult has an error message of: {Error SQL0: Cannot open database "DeployTestDB" requested by the login. The login failed.Login failed for user 'myUsername'.}

Upvotes: 4

Views: 5453

Answers (1)

Steven Green
Steven Green

Reputation: 3507

Take a look at the DacServices class in Microsoft.SqlServer.Dac.dll.

The code would look something like this:

using Microsoft.SqlServer.Dac;

class Program
{
    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);
        }
    }
}

Upvotes: 5

Related Questions