SSDT: Trick to Not Run Pre/Post SQL on Publish
We’ve run across the need to avoid running certain scripts when publishing a brand new copy of a database from a SQL or DB Project from time to time. Often, those pre/post deploy scripts are designed to be run only against an existing system and don’t make sense for a brand-new, empty database.
We’ve worked around this by using a SQLCMD variable called DeployType and setting that variable to “New” for any build operation that will be used only to create the initial database. The code snippet for our Post-Deploy script looks something like the following:
— Place scripts here if they should only run for existing/working DB
IF ( ‘$(DeployType)’ ‘New’ )
BEGIN –Run scripts
PRINT ‘Deploying scripts.’
END
–Security – runs last
:r .\SecurityAdditions\SecurityAdditionsWrapper.sql
The above script will look at the DeployType variable. If it’s set to “New”, it will ignore all of your deploy scripts in that block. If it’s set to any other environment, it will run the appropriate scripts if any exist. You can use this to control which environments will use certain scripts as well.
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.
hi Peter
I've groups of developers working on same database.
each group is working on a specific DB schema.
I make one .sqlproj for each group and separate them.
some project referenced from others and each project has
self pre\post deployment script.
I don't know how to publish these dacpac to production system.
please help
thanks in advance
Good question. We don't normally separate out our projects into separate schemas and try to combine them later. If you have references set up to “same database, same server” with all of the other objects you should be able to use that to publish, but you might be better off pulling everything into the same project and working from there. Alternatively, you might look at something like Jamie Thomson suggests here: http://sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/deployment-of-client-specific-database-code-using-ssdt.aspx. It's not exactly what you're doing, but does sound a little close.
Publishing should be the same as any other SSDT SQL Project, though you might want to disable the “drop objects not found in project” option before publishing.