When you deploy your Sql Server code, there are two approaches, there is the manual way and the automated “compare & deploy” way.

Manual

The manual way is where you track what changes you have made and when you want to release, build a script or set of scripts which contain the updates. If you are modifying objects then you should use alter or drop/create and you need to manually create rollback scripts (should you need them).

I don’t like the manual method because:

• If you work in a team, someone needs to co-ordinate what changes are being deployed
• It is really easy to forget something and not include it in the deployment
• The change scripts for each deployment are stored and to get to a particular point in time, you need to apply the base scripts and then all the updates
• Manual = Error prone, I have no statistics to show you but it is a fact, believe it

I do like the manual method because sometimes you want to do something that a tool cannot, for instance you want to archive data so you can do things like migrating it in chunks or in smaller transactions. You might have a better way of adding a table or be better at working out what operations can be done on or offline such as changing some columns metadata.

Compare / Merge

I like the compare merge method because:

• I can work in a team, we can all check our code in and a tool works out what code to deploy.
• No one has to gather the changes and can forget to include something, if it is checked in then it goes in the deploy
• I can include whatever manual scripts I like because I “build the deployment pipeline and I decide”
• I can use the same system to generate rollback scripts automatically
• I trust the tools I use (sqlpackage.exe or sqlcompare.exe) and they are not error prone
• It is really easy to get a database into the current state, like 1 step setup easy

I don’t like the compare merge method because I sometimes have to think about changes and manually script them.