Making Ssdt Just Generate a SQL Script (And Not Deploy a Database)

Making SSDT just generate a SQL script (and not deploy a database)

To generate a script from an offline data project in SSDT rather than deploy to a target database, configure the project settings as shown below

Making SSDT just generate a SQL script (and not deploy a database)

To generate a script from an offline data project in SSDT rather than deploy to a target database, configure the project settings as shown below

How to generate SSDT change script using MSBuild in CI environment?

msbuild.exe $SqlProjPath /p:SqlPublishProfilePath=$SqlPublishProfilePath /p:UpdateDatabase=false /t:Build,Publish where the most important argument is UpdateDatabase and when it is false, it will just generate the script without publishing

UPDATE (based on the comments): as far as I understand that connection to database is wanted to avoided. This is actually not recommended approach as normally you can't be 100% sure what exact version of database is currently on production (or whatever environment you are going to deploy to) as there could be million reasons why it drifted, most often because of some manual changes directly to database. That's why it is advised to compare to it. In any case, when working with SSDT you always need to have some database or dacpac to compare against. So, these are the options I can see:

• Compare with real database
• Extract dacpac from real database and compare against it
• Checkout previous project version from source control, build project and compare current state with built dacpac
• Save last deployed dacpac in version control and use it to compare. After deployment commit new version of dacpac

The first option is most advisable and going down each approach getting worse.

SSDT publish script recreates procedures with no changes

Update:

By renaming the .dacpac as a .zip and extracting the model.xml I could see the <HeaderContents> of some of the procedures had (LF - the Line Feed character) in them.

This made me realise that for some reason all my .SQL files had unix line endings (LF) instead of windows line endings (CR LF). Converting all the files to window line endings (using notepad++) solved the problem.

Original:

OK, it looks like most of them are due to string constants in the scripts containing new line characters. Replacing them with their manually defined characters means they're not longer picked up for redeployment.
i.e.

SET @doesnt_work = 
'FOO
BAR'

can be replaced with

SET @works = 'FOO'  + CHAR(13) + CHAR(10) + 'BAR'

Note: This is more of a workaround than a solution, and hopefully someone can suggest a better way to do this...

Deployment using TFS 2012 of SQL Project Not including Post deploy script

database Project not running Post Deployment scripts

See the linked question for the answer. I removed comments. When I did this the Build Action reverted to "None" I checked later and fixed this by setting to "Post-Deploy" and without the comments, it worked.

---

Related Topics