How can I keep database schema under version control?

This article describes how to use the advantages of version control in the process of database development. In this work example we used SVN as the version control system, however, you can use SourceSafe, CVS or any other version control system.

To save a database schema as a file, it is convenient to use snapshots that SQL Examiner generates. In this example we used a folder C:\Snapshots, that serves as the local repository. This folder may be also specified in Default Snapshot Location in the SQL Examiner settings.

Run SQL Examiner and compare the database, that will be altered, with an empty database. In this example we compared AdventureWorks that is stored on dev.tulasoft.com.

Select Save Snapshot of AdventureWorks in the File menu and save the snapshot in C:\Snapshots\AdventureWorks.sesnap.

Open the folder C:\Snapshots in the window folder. The AdventureWorks.sesnap file hasn’t been added to the repository yet, so we add it now. Select SVN Commit in the context menu.

The file is added to the repository now.

Now when we have saved a copy of the database schema in the repository, we can change any objects, and, for example, delete the procedure [HumanResources].[uspUpdateEmployeeHireInfo].

Beginning with the version 2.0.0.71, it is possible to create database snapshots in a command line. It is not necessary to launch SQL Examiner to create snapshots, simply perform the following command:

SqleCmd.exe /server1:dev.tulasoft.com /db1:AdventureWorks
/WinAuth1 /SaveSnapshot:c:\Snapshots\AdventureWorks.sesnap /Force

You can copy this command to the cmd-file and execute when necessary.

If you open the C:\Snapshots folder now, you will see that the file has been changed: it is indicated with an exclamation point in the file's icon.

To detect the difference between the existing schema and the schema that is saved in the repository, you can use SQL Examiner, alongside with the built-in DIffViewer. Customize the following settings: choose Tortuise SVN » Settings in the context menu, then choose DiffViewer in the dialog box and click "Advanced" in this tab.

In the dialog box that appears, specify the program that will open the files with the sesnap extension, the SQL Examiner snapshots: in this example it is Program Files\Sql Accessories\SQL Examiner Suite\SQLExaminer.exe.

If you choose Tortuise SVN » Diff, for AdventureWorks.sesnap, it launches SQL Examiner that automatically compares the two versions.

If you need to revert to an earlier state of the database schema, choose Tortuise SVN » Revert in the context menu.

In SQL Examiner, compare the snapshot with a live database and synchronize the changes.