Friday, February 24, 2012

Another way to check database version before automatic deployment

With VS2008 (GDR) / 2010 / 2011 you may use automatic deploy scripts built with VS and based on vsdbcmd.

If you track the database version inside of your database (for example, in a separate table), you may need to check the current database version before incremental update – in some cases you may support the update only for dedicated versions and above and not for older versions.

You can force deployment break in the pre-deployment script after check for database version – if the database version is not supported for update:

--- get database version from current database

select @CurrentVersion =[PARAM_VALUE] from [MySystemParameters]

--- check the version and break the deployment if the version is not supported

--- the sample is simplified: in real life you will check major version, minor version, build number etc.

if not ( @CurrentVersion >= @MinSupportedVersion)
      raiserror ('Current database verison is not supported and cannot be updated',18,0)
      set noexec on

When you start the script on the target box, the deployment break looks like following:



No comments: