Defining Post Deployment Scripts in the SQL Server Data Tools

By: Mike Bishop   |   Updated: 2013-03-01   |   Comments (9)   |   Related: > Tools

Problem

Maybe I jumped the gun a bit, but I've started using SQL Server Data Tools (SSDT) in Visual Studio 2010. Since then I've become SSDT's biggest fan. The tool is just so much easier to use than the old VS 2010 database projects. But unfortunately, for some reason, I can't seem to get my post deployment scripts to work when I publish the project to a live database. I have several post deployment scripts that populate different lookup tables in my database. But when I look at those tables in SQL Server Management Studio after I publish, the data just isn't there. Any help you can give would be much appreciated.  Check out this tip to learn more.

Solution

Your enthusiasm is understandable given the overwhelming positive reaction to SSDT among SQL Server developers. It's great, for example, to have a designer view and a T-SQL view (shown below) when you're designing or refactoring SQL tables. The fact that changes in both views are immediately synched and checked for syntax makes for a pleasant and efficient user experience.

As you probably know, another reason for SSDT's popularity is the project type's flattened folder structure inside Visual Studio's Solution Explorer. SSDT introduces a new project template in Visual Studio (the *.sqlproj type) that effectively replaces the older VS 2010 database project type. One of the differences between the two is that the old project type had lots (and lots!) of folders along with lots (and lots!) of different script files. Here is an example of the old database project type in Visual Studio.

Compare that to the new folder structure of an SSDT project below. You should also keep in mind that now (thankfully) the object scripts related to table creation are not scattered across half a dozen folders as was the case for *.dbproj projects. Here's an example of the new SSDT *.sqlproj project type in VS Solution Explorer:

Notice that the old Post-Deployment and Pre-Deployment folders are gone. Instead, you get a nice, clean project structure that is dynamically tailored based on the existing objects in your imported database. So if the Northwind database contained user-defined functions at the time of the import, there would be a folder for those objects as well. Of course, that doesn't address the missing deployment folders (I know, I know, you loved those folders, didn't you?). Visual Studio, of course, makes it easy to add your own folders to any project type, so if you really want to segregate post-deployment scripts in a separate folder then you can certainly do so.

It's good to know too that, if you go through the exercise of converting an old database project to the new SSDT project type, rest assured that the existing Script / Deployment folder structure and scripts will be preserved in the new SSDT project.

So How Many Post-Deployment Scripts Do You Have?

Actually, the only correct answer to this question is "one". (And, yes, Visual Studio will warn you if you try to add more than one post-deployment script to the project.) By convention, many people name this file something like MainDeployment.sql. Within this sole post-deployment script, typically the file contains a list of SQLCMD :r read commands that include (parse) additional external scripts. The post deployment script and each of the included external scripts should have the appropriate script file properties in order to work the way you want. "So what are these properties?", you may ask. Well, assume that I have added a folder structure of Scripts | Post-Deployment to my SSDT project as pictured below.

I can now right-click on the Post-Deployment folder, click Add New Item, and select User Scripts from Installed Templates. Clicking the Post-Deployment Script template adds the script to my project.

Once the template file is added, simply right click on its name in Solution Explorer and select Properties from the context menu. We can see that one of the file properties for the post-deployment script defines a Build Action of PostDeploy. Depending on your build requirements, you can choose either to Copy or to Not Copy the script file to the Output Directory.

As I mentioned before, sometimes developers like to organize several files in the Post-Deployment folder and include them from the post-deployment script file via the SQLCMD :r read command. In our last example, I've added a separate, external script file called AlterDbSizeGrowth.sql to the project in order to alter size and growth characteristics for the Northwind database.

/* Please do not change the database path or name variables. It will be properly coded for build 
and deployment.  This example is using sqlcmd variable substitution. */
ALTER DATABASE [$(DatabaseName)]
 MODIFY FILE
 (
  NAME = [$(DatabaseName)],
  SIZE = 6MB, FILEGROWTH = 1MB
 )
GO     
ALTER DATABASE [$(DatabaseName)]
 MODIFY FILE
 (
  NAME = [$(DatabaseName)_log],
  SIZE = 2MB, FILEGROWTH = 1MB
 )
GO  

For this script, since it is an external file which is 'included' from the post-deployment script, the appropriate file properties are significantly different. Here the Build Action is None as shown below.

Next Steps

• Always check the Build Action for all scripts in a Post-Deployment folder.
• Make sure the Build Action is PostDeployment for the sole post-deployment script.
• Make sure the Build Action is None for any external script file that is included from a post-deployment script.
• Learn more about the tools to available in SQL Server.

About the author

Mike Bishop is a Boston-based DBA and data architect with 20+ years of database management experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips