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.
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 :rread 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)],
SIZE = 6MB, FILEGROWTH = 1MB
ALTER DATABASE [$(DatabaseName)]
NAME = [$(DatabaseName)_log],
SIZE = 2MB, FILEGROWTH = 1MB
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.
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.
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
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.
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?
Wednesday, April 17, 2013 - 9:28:02 PM - Mike Bishop
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!
Wednesday, November 13, 2013 - 6:18:17 AM - Namrata
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
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?