But what version is the database now?
- by BuckWoody
When you upgrade your system to SQL Server 2008 R2, you’ll know that the instance is at that version by using the standard commands like SELECT @@VERSION or EXEC xp_msver. My system came back with this info when I typed those:  Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)                      Index          Name          Internal_Value          Character_Value                        1          ProductName          NULL          Microsoft SQL Server                        2          ProductVersion          655410          10.50.1600.1                        3          Language          1033          English (United States)                        4          Platform          NULL          NT INTEL X86                        5          Comments          NULL          SQL                        6          CompanyName          NULL          Microsoft Corporation                        7          FileDescription          NULL          SQL Server Windows NT                        8          FileVersion          NULL          2009.0100.1600.01 ((KJ_RTM).100402-1540 )                        9          InternalName          NULL          SQLSERVR                        10          LegalCopyright          NULL          Microsoft Corp. All rights reserved.                        11          LegalTrademarks          NULL          Microsoft SQL Server is a registered trademark of Microsoft Corporation.                        12          OriginalFilename          NULL          SQLSERVR.EXE                        13          PrivateBuild          NULL          NULL                        14          SpecialBuild          104857601          NULL                        15          WindowsVersion          393347078          6.0 (6002)                        16          ProcessorCount          1          1                        17          ProcessorActiveMask          1          1                        18          ProcessorType          586          PROCESSOR_INTEL_PENTIUM                        19          PhysicalMemory          2047          2047 (2146934784)                        20          Product ID          NULL          NULL                  But a database properties are separate from the Instance. After an upgrade, you always want to make sure that the compatibility options (which have much to do with how NULLs and other objects are treated) is at what you expect. For the most part, as long as the application can handle it, I set my compatibility levels to the latest version. For SQL Server 2008, that was “10.0” or “10”. You can do this with the ALTER DATABASE command or you can just right-click the database and select “Properties” and then “Database Options” in SQL Server Management Studio.  To check the database compatibility level, I use this query:    SELECT   name, cmptlevel    FROM   sys.sysdatabases   When I did that this morning I saw that the databases (all of them) were at 10.0 – not 10.5 like the Instance. That’s expected – we didn’t revise the database format up with the Instance for this particular release.   Didn’t want to catch you by surprise on that. While your databases should be at the “proper” level for your situation, you can’t rely on the compatibility level to indicate the Instance level.  More info on the ALTER DATABASE command in SQL Server 2008 R2 is here: http://technet.microsoft.com/en-us/library/bb510680(SQL.105).aspx
Share this post: email it! |  bookmark it! |  digg it! |  reddit! |  kick it! |  live it!