Thursday, January 21, 2021

SQL version control - schema snapshots

Database version control is one of the most critical tasks in ensuring the safety and reliability of the software development process and the operation of data-driven applications. Every code deployment coincides with a particular state of the database supporting it - the code and the database must be in sync. If you roll your application back to a point in time you may also have to roll back the database schema changes or your application may become unstable or non-functional. 

Many database administrators and developers commit the database change script or the whole database create script in the repository together with the application code, and that serves the purpose, but it's far from being efficient or convenient. Our SQL Schema Compare tool offers a better way, schema snapshots

A schema snapshot in this context is a small encrypted file that contains 100% of the database schema information in it. You can manually take schema snapshots of a given database any time you decide to through the SQL Schema Compare GUI or, you can use the included schema compare Command Line utility to automate the process and take snapshots on a schedule. Furthermore you can automate the decision on whether you need to store the snapshot in the repository or not by comparing the current snapshot with the previous one: if there are no changes you can discard the new snapshot otherwise you commit it to repository. 

Utilizing schema snapshots you can then do any of the following:

  • Create a copy of the database (schema only). To do that, you create a blank database, then compare the desired source snapshot with the blank database and generate the "sync" script which will make the blank database exactly the same as the database from which the snapshot was taken. 
  • Compare any two snapshots to each other to generate the changes that have occurred in the time period between the two snapshots. 
  • Compare a snapshot directly to a live database and generate synchronization script to revert the database back to the state it was when the snapshot was taken. 


Post a Comment