Guest Blogger:

Feodor Georgiev

This is a guest post from Feodor Georgiev. Feodor has been working with SQL Server since 2002, specializing in database performance tuning, documentation, and scalability management. He also works as project leader and mentor on SQL Server and Business Intelligence projects on Microsoft-based solutions.

When he's not busy with his DBA work, keeping up with the latest SQL Server tricks, or sharing tips on forums, he writes articles on .

My previous article, How to compare two DacPacs using SQL Compare , described how to use theSQL Compare GUI to compare two DacPacs, and generate a synchronization script.

This article takes it one step further, showing how to use PowerShell and the SQL Compare command line to automate the generation of a synchronization script, to build or upgrade a database, starting from a DacPac.

As a DBA, you can easily be given a DacPac by a development team, encapsulating the latest database changes, which they wish you to release to a production database.

This is fine, but you’re probably accustomed to generating your deployment scripts using SQL Compare, inspecting the script, tweaking it, trying it out on staging, running tests to ensure that any problems you get will be subtle or unusual. You may need to run it past other people to get buy-in, such as the Technical Architects, or Governance, for compliance purposes.

In short, you need to generate a deployment script from a DacPac. Fortunately, all you need to do is to unpack the DacPac and then compare it to an empty database, or even an empty file, to generate a build script, or to the current production database, to generate an upgrade script.

Create the DacPac

The first thing we need to do is to download and install the latest version of SQL Server Data Tools (SSDT) in order to get the command-line file sqlpackage.exe . In this case, I’m using DACFx 17.0.

Having done that, we can create a DacPac. As described in myprevious article, a DacPac file is simply a binary file, a zipped directory, containing definitions of objects in a SQL Server database.

We can rename the extension to .zip to open the file and view the contents. Significantly, among other things, it contains an XML ‘model’ of the database’s metadata ( model.xml ).

Listing 1 shows the PowerShell script to extract a DacPac of AdventureWorks2014 . If you already have an example DacPac, then you can leave out this stage.

$MyInstance='ServerName\InstanceName'#the instance where the database is that you want copy from $MyDatabase='AdventureWorks2014' #the name of the database we want to turn into a DacPac $WhereToPutIt="$env:temp\$MyDatabase.DacPac" #choose a suitable location #extract a DacPac & "$env:programfiles (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" @( '/Action:Extract',#extract it "/SourceServerName:$MyInstance", #The SQL Server instance "/SourceDatabaseName:$MyDatabase", #The database to script "/TargetFile:$WhereToPutIt", '/p:ExtractAllTableData=false') # and the file to put it in

Listing 1: Extract a DacPac from the AdventureWorks database

In Listing 1, make sure you have the correct server name, database name and path to the sqlpackage file. Also, keep in mind that the DacPac will be saved in the AppData folder of the current user; the PowerShell output will remind you of the exact location, like this:

Connecting to database 'AdventureWorks2014' on server …..
Extracting schema
Extracting schema from database
Resolving references in schema model
Successfully extracted database and saved it to file 'C:\Users\UserName\AppData\Local\Temp\AdventureWorks2014.DacPac'. Unpack the DacPac

Now we have a DacPac we can use, we’ll unpack it so that we can get at the model.sql file that is created as part of the unpacking process.

add-type -path "$env:programfiles (x86)\Microsoft SQL Server\120\DAC\bin\Microsoft.SqlServer.Dac.dll" #change to suit your system $MyDatabase='AdventureWorks2014' #the name of the database DacPac file $WhereToPutIt="$env:temp\$MyDatabase.DacPac" #the full path to the dacPac $WhereTheUnpackedVersionIs="$env:temp\unpacked" $DacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($whereToPutIt) $DacPackage.unpack("$WhereTheUnpackedVersionIs")

Listing 2: Unpack the AdventureWorks2014 DacPac

If you take a look at the contents of the model.sql file (in my case, created in the folder “ C:\Users\Bob\AppData\Local\Temp\unpacked “), it looks rather like a plain build script:

Automating DacPac deployments using the SQL Compare command line and PowerShell

Figure 1

However, if you attempt to run the script, you’ll see all kinds of errors since it doesn’t have the objects in the correct dependency order.

So while the model.sql file does contain the schema definition of our database, we need to use SQL Compare to turn it into a runnable deployment script.

Generating a database build script from a DacPac

In Listing 3, we first create a blank (empty) .sql file, called DummyDest.sql , and then get SQL Compare to compare this dummy file to our model.sql file.

It will cheerfully do this, and produce a synchronization script to make the database represented by the blank file into the database represented by the build script, but with all the transaction rollback on error and other things you would expect from a good deployment script.

In short, what we end up with in the ReleaseCandidate.sql file is a really good build script.

Set-AliasSQLCompare 'C:\Program Files (x86)\Red Gate\SQL Compare 12\SQLCompare.exe' -ScopeScript #change to suit your system $FolderName= "$env:temp" #Where I store my DACPAC -no terminating slash! $DestFolderName= "$FolderName\dest" #dummy destination $MySourceScript='ReleaseCandidate' New-Item $DestFolderName -ItemTypeDirectory -Force #create the dummy destination if(![System.IO.File]::Exists("$DestFolderName\DummyDest.sql")) {' '>"$DestFolderName\DummyDest.sql"}; #and create the dummy SQL file sqlcompare "/scr1:'$FolderName\unpacked'" "/scr2:$DestFolderName" "/scriptFile:'$FolderName\$MySourceScript.sql'" /force

Listing 3: Create a build script from a DacPac by comparing to an empty file

If you’re unsure where to find one of the files produced by this script, just highlight the appropriate path, such as “/scriptFile:’$FolderName\$MySourceScript.sql'” (including the quotes, and then execute just that section in PowerShell.

PS Z:\> "/scriptFile:'$FolderName\$MySourceScript.sql'" /scriptFile:'C:\Users\Username\AppData\Local\Temp\ReleaseCandidate.sql' PS Z:\> Generating a database upgrade script from a DacPac

It isn’t that often you’ll be given a DacPac and asked to create a new database. More often, you’ll be asked to make changes to an existing database. It turns out that this is even easier.

If you can access the production server, then sim

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

主题: SQLPowerShellXMLSQL Server
tags: DacPac,database,script,file,SQL,sql
本文标题:Automating DacPac deployments using the SQL Compare command line and PowerShell

技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(160)