A customer asked if they needed to restore a database from backup to compare the schema in a database. They don’t and this post shows that.

This is part of a series of posts on SQL Compare.

Setting Up a Comparison

When I open SQL Compare, I see a screen that looks like what I’ve shown below, with a database to database comparison.

At the top, to the left of “Source”, there is a drop down arrow. If I pick that I see these choices: database, backup, snapshow, scripts folder, SQL Source Control, SQL Change Automation, Flyway. Those last 3 are project types for Redgate tools.

If I select backup, I get a dialog where I can add my backup set files. I can add full or diff backup files, but not transaction log files. If I click the “+Add backup set flies”, I get a file picked, and I can find a backup file.

Once I pick one, I see it in my list. I can now clear the list or add more files. The details of how this work are documented at: https://documentation.red-gate.com/sc/working-with-other-data-sources/working-with-backups

Once I have my backup, I’ll set the target, in this case a copy of Northwind that I’ve altered and called Westwind. This is on my local instance.

When the comparison completes, I see the differences. This was without any sort of restore on my instance. Note that the top left icon for Northwind_FullRestore has a different icon. I have this database on this instance, but it’s different than the backup.

If I expand the results, these look like any comparison. I see those things that are the same, only in one or different. In this case, as we are trying to make the target look like the source, those objects in my db and not in my backup would be dropped if I deployed all changes.

Summary

This is a short demo of using a backup as a comparison source against a database. I haven’t really shown a flow or scenario, but I’ll do that in another post. This is just a short proof that this works.

SQL Compare is an amazing tool that millions of users have enjoyed for 25 years. If you’ve never tried it, give it an eval today and see what you think.