Post reply

DatabaseVersion field in the RESTORE HEADERONLY command

  • February 27, 2010 at 5:17 pm

    #217125

    I am writing a custom restore script and one of the things I want to do is verify that the files being restored are for the proper version of SQL Server. I see that in the native header there is information on the server version major, minor and build but there is also a field called DatabaseVersion and on SQL 2005 it gives a number like 611.

    The problem I'm running into is when I try to extract the header information from Red-Gate they only provide the DatabaseVersion number of 611. That is fine except I don't know how to correlate that back to the actual version that the server supports.

    Is there some mapping between SQL Server versions and what database version numbers they support so I can programmatically validate this?

    Thanks,

    John

  • February 28, 2010 at 1:09 am

    #1126217

    The general rule is that you can always upgrade (so 611 onto a server with databases in 612 or 655) but you can never downgrade.

    There's a partial list here: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx (towards the bottom of the page)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 28, 2010 at 8:19 am

    #1126248

    That's perfect, thanks!

    I still have two more questions though.

    1) Is there some kind of serverproperty that returns this version number of the databases it will generate or do I just need to know this list? Is it safe to check the version of model and just use that?

    2) When does SQL2005 start creating databases with 612 instead of 611? How do I know when 612 is required?

    Thanks,

    John

  • February 28, 2010 at 9:34 am

    #1126267

    JohnnyDBA (2/28/2010)

    1) Is there some kind of serverproperty that returns this version number of the databases it will generate or do I just need to know this list? Is it safe to check the version of model and just use that?

    No server property that I know of, check Books Online for the ServerProperty and DatabasePropertyEx functions. Checking model (or any of the databases in fact) should be safe. This isn't like compatibility level where different databases can have different values on the same SQL instance (other than the 611/612 mess)

    2) When does SQL2005 start creating databases with 612 instead of 611? How do I know when 612 is required?

    A SQL 2005 database is version 612 if vardecimal storage is enabled, 611 if it is not. Either can be restored to a SQL 2005 SP2 server, only 611 can be restored to SQL 2005 RTM/SP1. See http://msdn.microsoft.com/en-us/library/bb326755.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 28, 2010 at 9:52 am

    #1126272

    Thank you, you have been extremely helpful

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply