How to create incremental drop script by using MS Build and SQLPackage utility

1.     Background

 The purpose of this document is to provide a way of creating incremental drop script with respect to destination database without any manually intervention in the DB project. We are utilizing MSBuild and SQLPackage utility to create incremental drop script. SQL Server 2012 DB project always create/refresh“.dacpac” file whenever we build the project. The .dacpac file extension stands for Data-Tier Application Schema File Format Structure Specification. A .dacpac file consists of a package of XML parts that represents metadata of the data-tier application and SQL Server object schema.

2.     Step by Step procedure to create incremental drop script

1.       Take the latest SQL DB project from VSTF location- $/MyVSTFServer/DistributionDatabase and copy the project to appropriate location e.g. E:\DistributionDatabase.

2.       Go to windows command prompt and change the directory to where MSBuild.exe resides. MSBuild.exe resides in C:\Windows\Microsoft.NET\Framework\v4.0.30319

  

3.       After changing the directory, run the below command that create/refresh the DisributionDatabase.dacpac file :

MSBUild E:\DistributionDatabase\DistributionDatabase.sqlproj/property:Configuration=Debug

Go to E:\DistributionDatabase\bin\Debug folder where we can see the latest DAC package file (.dacpac) is created. Below is the screenshot showing latest .dacpac file after building the project:

4.       Now we need to create drop script with respect to target database. Here we are using SQLPackage utility to create the script.  Go to the directory where sqlpackage.exe resides.

The sqlpackage.exe resides in C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin folder

5.       Use the below command to create drop script w.r.t  target database:

Syntax:

SqlPackage.exe /a:Script /sf:".dacpac file location" /tsn:"Target Server" /tdn:"Target DB" /op:"path for output script file" /Variables:StagingDatabase="Staging DB Variable value "

e.g : SqlPackage.exe /a:Script /sf:"E:\DistributionDatabase\bin\Debug\DistributionDatabase.dacpac" /tsn:"TK5AITKYESTEST2" /tdn:"Vishal_Testing" /op:"C:\Users\v-vijhar\MySQl12345.sql" /Variables:StagingDatabase="Vishal_Testing_Staging"

Note:: We can use variables also in the command line.

 6.       Go to output file location and check whether output file is created or not :

Below screenshot showing “MySQL12345.sql” is created.

7.       Open the newly created .sql file and check the script:

 Below fig showing deployment script. The below script showing empty file as we have not changed anything in the solution. If we do any changes in the project solution then only those changes will reflect in the incremental drop script.

3.     Conclusion

By using above mentioned steps, we can create drop script (incremental) with respect to target database. The deployment script will contains all the DB objects which are not available in target DB or recently updated in the DB project.

--------------------------------End of Document-----------------------------------