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.
hi Peter
ReplyDeleteI'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.
DeletePublishing 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.
Hi Peter,
ReplyDeleteI tried this but it didn't work due to go statements within each sub-script.
Regards,
Tack
What sorts of errors do you get? Are those sub-scripts set to "include in build" (they should be set to "None")? Can you provide examples? I'd also recommend hitting up https://stackoverflow.com/questions/tagged/ssdt to post some examples and reach a broader community.
Delete