The benefits of compare-to-backup in SQL Compare

Jerry Hung is a DBA at eMedia Interactive. Before that he had a role familiar to many DBAs, a programmer/analyst/database developer/DBA job. Jerry graduated from the University of Waterloo in Waterloo, Canada, and spent five years in the university's co-op program.

In this article, he talks about his experience using compare-to-backup in SQL Compare.

The data I manage supports the core business of eMedia Interactive, www.restaurantica.com, a community site featuring thousands of reviews and listings of more than 400,000 restaurants in North America. Our data is not critical compared to, say, a banking system, and it doesn't strictly require 100-percent uptime. But, it is important that our databases are available to our users whenever they visit the site.

We currently maintain four databases, totaling approximately 20 GB in size. They contain restaurant data, user reviews of restaurants, user/membership support, full-text for searching, and spatial for local/proximity search.

My job is to monitor SQL Servers and maintain backup/restore, configuration and security. I also try to tune and optimize our code base for performance, and work with developers on stored procedures and database design.

As our software changed and solidified, the need to generate a diff/synch script became apparent. Every time we release new front-end code, a DB sync script needs to go with it if there are any changes. This means with each release, I compare the development database with production backup, check the sync script into TFS, and generate a build (application plus DB script).

eMedia Interactive has a license for Red Gate's SQL Toolbelt, so I've used SQL Compare V7 for database migration. SQL Compare V7 is easy to use, and its support of SQL Server 2008 was crucial as we updated our platforms.

Recently, I've found that I can use the compare-to-backup feature in SQL Compare V7 to achieve additional benefits, including:

• Greater accuracy
  Comparing the development database with the verified and only copy of production backup ensures that you get only the expected changes, not unwanted or unexpected ones.
• Time savings
  Saves you from restoring to both SQL Server and SQL Compare.
• Ease of mind
  No need to worry about finding a server to restore you just need to know the location of the backup.

SQL Compare does what it does very well, and the team behind it at Red Gate are friendly and very helpful. That's why we use SQL Compare and love it.