Best Practices for storing a database schema version in SQL Server?

Question

I have an app which will be deployed on production PCs with SQL Server. I want to be able to store and retrieve a version of the schema in my database. I'm interested in best practices to be able to accomplish this, with the following main goals:

1. Able to store and easily retrieve a version number of the database.
2. Hidden or more difficult to find and manipulate by clients.
3. Able to be edited/changed when we create a new version.
4. Backing up the DB or detacting the DB keeps the version # for forensics.

I wish there were a way to store a "version" in the metadata or not a normal table, which could be accessed/set through a system stored procedure.

Any ideas or best practices?

EDIT: One option I found which may be promising is to use SQL Server Extended Properties, to put a key|value assigned to the DB with "Schema_Version" and the version number. It isn't encrypted (but the value could be), and isn't hidden, but at least is removed from the actual DB structure which some of our users and field personnel browse (to my frustration! :) )

Answer 1

I'm the product manager for SQL Source Control and SQL Compare at Red Gate. We had to solve this very same problem as our tool needs to know which version the databases were at in order to select the appropriate migration scripts to build the full deployment script.

We considered a version table, which is the most commonly devised home-grown solution. However, from our research we learnt that users wanted to keep the set of database objects 'unpolluted' so we opted for the database level extended property. We append this to the scripts as follows:

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))   EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL 

When the database is loaded into SQL Compare, it performs a check to ensure that the version that it claims to be corresponds to the version as stored in source control.

Hope this helps!