If your system includes a SQL Server database, your process is probably complex. What we propose here is a simple process that does the job in a much better way.

If you're a developer or a DBA working on a SQL related project, you and your team probably work like this:

1. Whenever somebody makes a change to a database object, you create a change script and store it in a source control system.
2. At the end of the project, you collate all the different scripts and create a large script.
3. You then test the final script on a copy of the production database to upgrade it to the new version.
4. You then debug the script.

This is a great process, you're told, and you'll hear many consultants recommend it. True, but only in theory. In practice, you've probably found that:

1. Developers don't always create the scripts when they modify the database.
2. When they do, they don't test them, so they don't work.
3. You end up with many redundant scripts (if a column is added to a table, then removed, that's two unnecessary scripts).
4. You have no way of knowing if the script has made all the necessary changes. The only way to find out is to test your application and see where it crashes.
5. It's extremely time consuming and inaccurate. How much time do you spend creating and debugging the final migration script? Add that to the time each developer spends creating individual scripts for each change, and you're wasting a massive amount of developer resource.

Processes are there to save time (in the long as well as the short term), improve quality and help you work. If you're following the process we've described above, you've probably realized that this process fails on all three counts.

So, what's a better way of working? If you haven't already looked at it, we recommend you try SQL Compare. Rather than create individual SQL scripts whenever you change a database object, run SQL Compare when you've made all the database changes. It will show you the differences between your development and production databases and create a script to migrate from one to the other. It will save you hours of tedious work, and pay for itself after its first use.