Earlier I posted about how much I love SQL Packager™ by Red Gate Software. In my humble opinion, this is the ultimate way to package up the contents of a database (schema and data) and move it to another server. I know there are other ways of accomplishing it, but SQL Packager is much faster and much more reliable in my opinion. I use it constantly now.
Today, Father's Day of all days, I needed to push some database changes up to a production database. Normally I would have completed the changes by hand in about two hours, but I thought I would give SQL Compare™ and SQL Data Compare™ a shot.
As you can see, these two products go hand-in-hand when you need to compare and synchronize both the structure and data in a SQL Server database, which is what I needed to do today.
First, of course, you have to run SQL Compare to get the structures the same. Just like with SQL Packager, you have a simple wizard-like interface that walks you through the steps of
- Selecting the two databases to compare and synchronize
- Choosing which items you want to sync
- Selecting the direction of synchronization (Test > Production in my case)
- Reviewing the script and summary to make sure everything looks cool
- Running the process
That's it! The only manual effort, which has to be done by hand anyway, is setting default values on fields being transferred that do not allow null. SQL Compare does not add a default value to non-nullable fields for you, which you could argue either way as good or bad. I tend to be conservative and like the fact that SQL Compare warns me of this potential problem and asks me to either 1) set the fields to allow nulls or 2) set default values on the fields. Very cool!
Next, you run SQL Data Compare to synchronize the data. Running SQL Data Compare consists of the same wizard-like steps:
- Selecting the two databases to compare and synchronize
- Choosing which items you want to sync
- Selecting the direction of synchronization (Test > Production again)
- Reviewing the script to make sure everything looks cool
- Running the process
When completed, the databases should be synchronized, assuming you synchronized everything. You can check out the snapshots and walkthrus on the Red Gate website, but all I can say is that once again, this incredibly monotonous task was reduced to nothing more than running both these wizard-like applications.
After practicing with the tools a bit to get familiar with them, it took me only 15 minutes to push the changes up to the production database. If you don't count the 45 minutes or so of practicing, I basically saved about 2 hours today. These are must have tools if you spend a lot of time working with SQL Server.