Defining Post Deployment Scripts in the SQL Server Data Tools

By:   |   Comments (9)   |   Related: > Tools


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.


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.

SSDT Table and T-SQL Designer

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.

Visual Studio *.dbproj file structure

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:

Visual Studio folder structure for *.sqlproj projects

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.

Add a new file to the project

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.

Visual Studio SSDT Solution Explorer

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.

Add New ITem - Post-Deployment Script template

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.

Script.PostDeployment1.sql Properites in Visual Studio

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)]
  NAME = [$(DatabaseName)],
ALTER DATABASE [$(DatabaseName)]
  NAME = [$(DatabaseName)_log],

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.

File Properties for AlterDbSizeGrowth.sql
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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Mike Bishop 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

Comments For This Article

Monday, August 21, 2017 - 7:05:34 AM - Juan Piaggio Back To Top (65112)

 Hi Mike,

What about I want to use conditionals, I mean, an external variable will tell be what set of scripts to run.

All this scripts has GO inside.

I tried to overcome this, but always exit the if clause because of GOs inside the external file


Thursday, February 6, 2014 - 12:56:52 PM - Mike Back To Top (29357)

Thanks Mike!'

if we plan to use the :r ./AfterDbSizeGrowth.sql  line in the post-deploy, shouldn't AfterDbSizeGrowth.sql file be flagged to "Copy to output Directory" = "Copy always" or "Copy if newer" instead of "Do not copy"?  It seems as though we would want the file available in the output so that it can be used.  Otherwise, how would the AfterDbSizeGrowth.sql ever be found?


please advise

Wednesday, November 13, 2013 - 6:18:17 AM - Namrata Back To Top (27481)


I would like to know what exactly the Build Action property and the copy to output directory property does in the properties window for a model.bim file in SSDT (Analysis Tabular Model).What is the difference between setting Build Action to Compile or None.Also the options available with Copy to output directory..Is there a particular way in which they must be set.??Thanks

Thursday, April 18, 2013 - 4:36:34 PM - Albert Back To Top (23429)

I haven't used DACPAC at all before, and started reading up on it when I ran into this article:

Seems that it recreates your entire DB when you make structural changes? Hm y DB's are all 50GB and up... That won't really be ideal... even though I like the idea!


Wednesday, April 17, 2013 - 9:28:02 PM - Mike Bishop Back To Top (23407)


I'm thinking re SCC, not to use the 'source' but instead to use the dacpac? Whad'ya think?

Monday, April 15, 2013 - 8:19:57 PM - Albert Back To Top (23360)

Hey Mike, Good article. I am however wondering, how do you manage this with say the next release? Would you overwrite your post deployment script with the new one since you can revert back to the older one using source control? Or would you manage these files by hand with version numbers in the name?


Thursday, April 11, 2013 - 11:05:31 AM - G. Schilsson Back To Top (23306)

I just wanted to let you know that this post was very helpful. I was banging my head against the wall wondering why my Pre- and Post-Deployment scripts weren't taking. BUILD ACTION was the missing piece. Thanks for the details, for it helped me find the devil.

Saturday, March 2, 2013 - 7:58:34 AM - Mike Bishop Back To Top (22524)

Ha-ha-ha.  I hear you Armando.  Funny how they make the t-sql so different that that in SSMS.  Oh well.

Friday, March 1, 2013 - 11:57:39 AM - Armando Prato Back To Top (22508)

Nice tip, Mike!

Personally, I hate the dual Design/T-SQL window.  I just like looking at the script's SQL itself.

If anyone out there hates it too, you can change the behavior by right clicking a .sql file
and specifying "Open With" and choosing to open with an alternate T-SQL editor.   That should open all
subsequent files without the Design window.

get free sql tips
agree to terms