Tuesday, April 5, 2016

Schema Snapshots – how to preserve the history of database schema changes

What did my database schema look like a month ago, a year ago? What has changed between then and now? If you are a DBA, or even if your title does not say DBA but you are playing the DBA’s role, you have most likely run into those questions more than ones. In most cases such questions are not simply a matter of curiosity but rather critical compliance related questions. As a DBA you need a way to quickly and efficiently identify the changes that have happened to your database schema during a given time interval.

Of course, one option would be to restore the database at that point in time and compare that restored copy with the live copy. However, depending on the size of your database that may prove to be a much more challenging task that it sounds.

Our “Schema Snapshot” command line utility that comes with our Schema Compare tool makes your job very easy. Here is what you can do:
  1. Create a batch file that contains the following three commands:
    • CD R:\DBSchemaArchive (replace this path with the path where you will be storing the schema snapshots)
    • set d=%date:~7,2%-%date:~4,2%-%date:~10,4%  (get the current date from the machine and put it on a variable which I am calling “d”. 
    • "C:\Program Files (x86)\xSQL Software\SQL Server Comparison Bundle v7\xSQLSnapshot" /s:xSQLDemo2014 /d:AdventureWorks /sp:AdventureWorks_%d%.snpx  (in this statement you will need to replace the server name and the database name – you can also provide credentials if you need to)
  2. Schedule the above batch file to run daily and you are done. The Schema snapshots have a very small footprint and normally take very little time to complete. 
The Schema Compare tool allows you to then compare any two of those snapshots, or any of the snapshots with the live database and see what has changed. 


Post a Comment