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]
   where
   [PARAM_NAME] = 'DBVERSION'

--- 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)
   begin
      raiserror ('Current database verison is not supported and cannot be updated',18,0)
      set noexec on
   end

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

x10sctmp

Enjoy!

No comments: