redgate SQL Source Control Revision Numbers

Anyone that’s developed with me in the last 7 years knows that I’m a HUGE fan of redgate. Honestly, now that I use their tools, I don’t know how I would work on MS SQL databases without them.

Recently, I’ve been taking a deep dive into SQL Source Control and SQL Compare. So far, I love it.

• SQL Source Control: Is a Microsoft SQL Server Management Studio plug-in which allows me to commit my database schema, static data, and migration scripts into version control.
• SQL Compare: Is a stand alone tool that allows me to compare and deploy schema changes. From a Database, Backup, Snapshot, Script Folder, or directly from source control to any combination there of.

In my case, I am using SQL Compare to deploy schema changes directly from source control, to my database.

One notable feature, is SQL Compare’s use of Extended Properties to track the version control revision number, in the database.

Here is an example of the script they use:

   1: DECLARE @RG_SC_VERSION BIGINT

   2: SET @RG_SC_VERSION = 13670

   3: IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))

   4:   EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL

   5: EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

   6: GO

This means you can figure out the revision number of your database by querying the Extended Property.

   1: SELECT Name, Value FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL)

Why does this matter?

Well, when the time comes to update a client’s database to the latest version, it’s as easy as.

1. Figure out the version of their database (using the script above)
2. Using SQL Compare: Set your “Source” to the Head of your source control, set the “Target” to the version of the database the client is at
3. Using SQL Compare: Compare the two database schemas, and generate a deploy script

So awesome.