Applies to: xSQL Schema Compare v7.0.0 – 9.0.2

Before I develop a scenario where xSQL Schema Compare snapshot comparison is necessary let’s explain what xSQL Schema snapshots are. An xSQL Schema snapshot is a compact file that contains all the schema information for a database. In simple words, an xSQL Schema snapshot is basically a picture of a database’s schema at a specific moment in time. These snapshots are very useful if you want to keep track and get a clear picture of the evolution of a database, by comparing the snapshots with each other or with the live database.

Scenario: Naturally, the question that arises is “Where and how would one use this feature?”. Let’s, for the purpose of this article, take the place of a software developer in a relatively big company. In most cases the development department is separate from data management which means that developers do not have any access to the live databases because these databases are managed by entirely different persons. So how would the developer go about updating the live database with the schema changes made in the development database after the development of a new feature is completed? Direct database comparison is not possible because no party has access to both databases. Simply sending an email to the DBA with the changes you need to be made is out the question because it’s error prone and for anything more than very minor changes, it is not feasible.

Solution: Use snapshots! Ideally, the DBA who manages the live database would create a simple job that periodically takes a schema snapshot of the live database and dumps it into a shared repository to which the developers have access. These snapshots would then be used by the developers for auditing and debugging purposes. In this case, you can use these snapshots to compare the development database with a snapshot of the live database, generate the synchronization script and send it to the DBA to execute it on the database. Let’s see how this is done.

For demonstration purposes I created a copy of the Northwind database and took a snapshot of the copy with xSQL Schema Compare. This copy will serve as the live database. Then I added a column on one of the tables in the original database which will serve as the development database. The picture below shows the snapshot in xSQL Schema Compare:

The comparison process for databases and snapshots is exactly the same. For the left database I am using the NORTHWND which has the extra column and instead of the right database I added the snapshot.

After choosing the database and the snapshot, I ran the comparison and xSQL Schema Compare found the difference in the ‘Categories’ table, for which, the left database has one additional column.

If I generate the synchronization script for the right database, it will generate this SQL Statement: