Whether we use a tool to generate upgrade scripts or we write manual test scripts for database changes there are a few things that we should bear in mind to ensure we do not break existing databases.

Why is it important?

When you upgrade an application you just compile and then deploy the code in a single operation, the old code is removed and the new code put in place, simple.

When you have a database you have all this data that you need to keep, you need to ensure that you are collecting the right data and you aren't damaging the data that is already there. For me the data in a database is really this organic thing, it grows and it shrinks. We really need to be careful when upgrading a database that we are doing the right thing, we do not drop a table here or there or update a whole table because of a missed where clause.

Process for managing upgrade scripts

I think there are two things we need to do:

• 1. Keep all our environments/databases in sync
• 2. Re-use the same scripts for each environment

1. Keep all our environments/databases in sync

This is an obvious one but that is really so often overlooked - when you have different environments such as dev, test, qa, production etc and all companies have a slightly different set, you really need to make sure that they are the same:

• When changes are made, push them up through each environment
• If an emergency fix is made in production, push it back up through from the beginning
• If a change is rolled back, remove it from everywhere it was deployed to
• Periodically check that the databases are the same, if they are not then find out how they got in that state and find ways to stop it happening

If you do not make sure that they are the same then when you are developing or testing or "productioning" you have no guarantee that what you are doing will work the same in any other environment which makes testing invalid.

2. Re-use the same scripts for each environment

If the databases are in sync then we can generate our deploy scripts early on in the process, most likely to a qa or production mirror environment and re-use the same script for each further environment until we get to production.

This means that we get the opportunity to validate that not only the change works in each environment but that the upgrade script works in each environment. I would strongly suggest you do this for all deployment methods whether that is manual upgrade scripts or compare + merge using ssdt or the redgate tools.