Custom table compare and deployment using SSDT

0
votes

I am stuck up in a situation where the database schema present in my SSDT / Database project has some less schema while the production database will have some more schema object. These additional schema object are create by customer only for his own purpose (for e.g maintenance).

in nutshell the SSDT / Database project contains all the schema related to application where as the product / customer environment contains application related schema as well as some more maintenance related schema object

Now i need to modify some of the application related schema object in the SSDT / database project but my worry is when i provide the build to the customer then the default nature of SSDT / database project is the compare the 2 database i.e source (customer database) and Target (SSDT / database project) and drop the object from source which are not present in target. therefore the object created by customer for maintenance purpose would get delete.

so is there any why where i can avoid such a case through SSDT / Database project setting.

Thanks in advance

1
sql-serverdatabasedeploymentdatabase-projectsql-server-data-tools

1 Answers

1
votes

There's an option for both SQLPackage and in your Publish Profiles that you can set to not drop objects if they aren't in the source. I use that regularly for our production database releases.

Check out http://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx for the SQLPackage reference. You'd want the parameters like "DropConstraintsNotInSource", "DropDmlTriggersNotInSource", "DropObjectsNotInSource", etc.

If you right-click your SQL project and select "Publish", then click the "Advanced" button, you'll see the various options for dropping/not dropping objects.

If you're concerned about this happening by accident, I'd set up a Publish Profile with the settings you want to use and then only use that to push your changes. You can easily save the publish options you're using when you choose to publish, then reference that later. Jamie Thomson has a great blog about that here: http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/09/publish-profile-files-in-sql-server-data-tools-ssdt.aspx