But what version is the database now?
Posted
by BuckWoody
on SQL Blog
See other posts from SQL Blog
or by BuckWoody
Published on Mon, 03 May 2010 13:33:51 GMT
Indexed on
2010/05/03
14:48 UTC
Read the original article
Hit count: 284
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, cmptlevelFROM
sys.sysdatabasesWhen 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
© SQL Blog or respective owner
