Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Defining Post Deployment Scripts in the SQL Server Data Tools

MSSQLTips author Mike Bishop By:   |   Read Comments (8)   |   Related Tips: More > 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.

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)]
 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.

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.


Last Update: 3/1/2013


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

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, March 01, 2013 - 11:57:39 AM - Armando Prato Read The Tip

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.


Saturday, March 02, 2013 - 7:58:34 AM - Mike Bishop Read The Tip

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


Thursday, April 11, 2013 - 11:05:31 AM - G. Schilsson Read The Tip

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.


Monday, April 15, 2013 - 8:19:57 PM - Albert Read The Tip

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?

Thanks,
Albert 


Wednesday, April 17, 2013 - 9:28:02 PM - Mike Bishop Read The Tip

@Albert

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


Thursday, April 18, 2013 - 4:36:34 PM - Albert Read The Tip

I haven't used DACPAC at all before, and started reading up on it when I ran into this article: http://infinitecodex.com/infinitecodex/post/2010/12/09/DACPAC-is-a-reason-to-upgrade-to-SQL-Server-2008-R2.aspx

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!

-Albert


Wednesday, November 13, 2013 - 6:18:17 AM - Namrata Read The Tip

Hi,

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, February 06, 2014 - 12:56:52 PM - Mike Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.