Using DATABASEPROPERTYEX:

 select DATABASEPROPERTYEX ('AdventureWorks2008R2','Version')

Using DATABASEPROPERTY:

    select DATABASEPROPERTY('AdventureWorks2008R2','Version')

Using sys.sysdatabases:

select name, version from sys.sysdatabases 
where name = 'AdventureWorks2008R2'

Using DBCC DBINFO command:

   DBCC TRACEON (3604);
    GO 
    DBCC DBINFO ('AdventureWorks2008R2')
    GO 
    DBCC TRACEOFF (3604);

/* DBINFO STRUCTURE:

DBINFO @0x000000001033D740

dbidbid = 5 dbistatus = 65544 dbinextid = 375672386 dbidbname = AdventureWorks2008R2 dbimaxDbTimestamp = 726000 dbiversion = 661 dbicreateVersion = 661 dbiESVersion = 0
dbinextseqnum = 1900-01-01 00:00:00.000 dbicrdate = 2011-07-07 14:54:07.707 dbifilegeneration = 0
dbicheckptLSN */

Using DBCC PAGE:

-- DBCC PAGE ('DatabaseName', 1, 9, 3) to look at the boot page contents
-- Boot page is always on page 9
DBCC TRACEON (3604);
GO 
DBCC PAGE (AdventureWorks2008R2, 1, 9, 3); 
GO 
DBCC TRACEOFF (3604);

/* DBINFO @0x000000001033A060

dbidbid = 5 dbistatus = 65544 dbinextid = 375672386 dbidbname = AdventureWorks2008R2 dbimaxDbTimestamp = 726000 dbiversion = 661 dbicreateVersion = 661 dbiESVersion = 0
dbinextseqnum = 1900-01-01 00:00:00.000 dbicrdate = 2011-07-07 14:54:07.707 dbifilegeneration = 0
dbicheckptLSN */

DATABASEPROPERTYEX

select DATABASEPROPERTYEX ('AdventureWorks','Version')

DATABASEPROPERTY

select DATABASEPROPERTY ('AdventureWorks','Version')

Using sysdatabases

select name, version from sys.sysdatabases where name = 'AdventureWorks'

Using DBCC DBINFO:

 DBCC TRACEON (3604);
 GO 
 DBCC DBINFO ('AdventureWorks')
 GO 
 DBCC TRACEOFF (3604);
 GO

inside the output you will find
you will find dbi_version which is inside DBINFO STRUCTURE

You can also read the page header by using DBCC PAGE:

DBCC TRACEON (3604);  
GO 
DBCC PAGE (AdventureWorks, 1, 9, 3); 
GO 
DBCC TRACEOFF (3604);

inside the output you will find
you will find dbi_version which is inside DBINFO STRUCTURE

Attach a database from an earlier version of SQL Server

you'll see these numbers in the error log as SQL Server reports

Error Message received when attaching an SQL Server 2008 database on to an SQL Server 2005 or lower versions

Msg 1813, Level 16, State 2, Line 1 Could not open new database 'DatabaseName'. CREATE DATABASE is aborted. Msg 948, Level 20, State 1, Line 1 The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

Here Version is 611

We can use xpmsver stored procedure which returns more details than just the version. exec xpmsver Executing procedure will display following output. It is truncated and formatted. Index Name InternalValue CharacterValue —– ————– ————– ————— 1 ProductName NULL Microsoft SQL Server 2 ProductVersion 589824 9.00.1399.06 3 Language 1033 English (United States)

In order to view just the product version, execute stored procedure option name. In this case, it will be ProductVersion. Here is the command followed by output. exec xp_msver ProductVersion 2 ProductVersion 589824 9.00.1399.06 Another way to check the version is use of @@VERSION command. It returns the product name, version, processor type along with date. SELECT @@VERSION Here is the output of the following command. Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) The service pack 1 in the end indicates the OS level service pack. The 9.00.1399.06 indicates that this is a SQL Server 2005 RTM install.

Option 1:

DBCC TRACEON (3604);
GO 

CREATE TABLE #DBCC ( 
    ParentObject VARCHAR(255)
    , [Object] VARCHAR(255)
    , Field VARCHAR(255)
    , [Value] VARCHAR(255) 
)  

Insert into #DBCC
     Exec ('DBCC DBINFO (''AdventureWorksDW2008'') WITH TABLERESULTS ')    

SELECT TOP 1 @@SERVERNAME as ServerName
, 'AdventureWorksDW2008' as DBName
, (SELECT Value FROM #DBCC WHERE Field = 'dbi_createVersion') as CreateVersion
, (SELECT Value FROM #DBCC WHERE Field = 'dbi_version') as CurrentVersion
, (SELECT Value FROM #DBCC WHERE Field = 'dbi_crdate') as CreateDate
FROM #DBCC

Drop Table #DBCC

 GO 
 DBCC TRACEOFF (3604);

1) Using DBCC Command - a) DBCC TRACEON (3604) -- looking page content

go DBCC PAGE ('AdventureWorks2008R2',1,9,3) GO DBCC TRACEOFF (3604) GO

DBCC TRACEON (3604);
GO

DBCC DBINFO ('AdventureWorks2008R2')

GO

DBCC TRACEOFF (3604);

check dbicreateverion value - this value is in which dbase has been created. dbiverion - current version of database.

2) select DATABASEPROPERTYEX ('AdventureWorks2008R2','Version')

3) select DATABASEPROPERTY('AdventureWorks2008R2','Version')

4) select name, version from sys.sysdatabases
where name = 'AdventureWorks2008R2'

Please check the below aqttached link for more information on verioning. http://sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

Internal version is for SQL Server 2008

We All know the SQL compatibility level provides partial backwards compatibility with previous versions of SQL Server to allow legacy code to continue to function on newer versions of SQL Server.

The currently available compatibility levels are:

• 60 = SQL 6.0
• 65 = SQL 6.5
• 70 = SQL 7.0
• 80 = SQL 2000
• 90 = SQL 2005
• 100 = SQL 2008

Setting a database in SQL Server 2008 to compatibility level 80 and then detaching it from the server won't allow it to be attached to a SQL Server 2000 instance. If we try it, we will get an error similar to the following:

AdventureWorks cannot be opened because it is version 655. This server supports version 539 and earlier.

The reason for this is that database internal version is for SQL Server 2008.The internal version value will be change once database is upgraded from Lower version of Server to Higher version of SQL Server.

The internal versions of current SQL Server versions are

• SQL Server 7.0 databases have version number 515
• SQL Server 2000 databases have version number 539
• SQL Server 2005 databases have version number 611/612
• SQL Server 2008 databases have version number 655

To find the internal version number of a database following methods can be used.

1.

 DECLARE @Str_Version VARCHAR(100)

 CREATE TABLE #Version(ParentObject VARCHAR (255)
    , [Object] VARCHAR (255)
    , Field VARCHAR (255)
    , [Value] VARCHAR (255))

EXEC master.dbo.SP_MSFOREACHDB
@Command1='USE AdventureWorks  INSERT INTO #Version 
                              EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'

SELECT TOP 1 * FROM #Version  WHERE Field='dbi-version'

drop table #Version

2.

USE AdventureWorks  
GO
SELECT name,   
       DATABASEPROPERTY(name, 'version') 
FROM   master.dbo.sysdatabases 
                  WHERE name = 'AdventureWorks'

OR

SELECT name,   
       DATABASEPROPERTYEX(name, 'version') 
FROM   master.dbo.sysdatabases 
                     WHERE name = 'AdventureWorks'

3.

   DBCC TRACEON (3604); 
    GO
    DBCC DBINFO
    GO
    DBCC TRACEOFF (3604);

4.

DBCC TRACEON (3604); 
    GO
    DBCC PAGE (AdventureWorks, 1, 9, 3);
    GO
    DBCC TRACEOFF (3604);

The output of this query will be like,

...... Record Type = PRIMARY_RECORD Record Attributes = Record Size = 1444

Memory Dump @0x000000001322C060

0000000000000000: 0000a405 8f028f02 00000000 00000000 †..¤.............

As we can see bolded integer value "8f02" interpreted as 0x028f = decimal 655 , which is the internal version number for SQL Server 2008 databases

5.

RESTORE HEADERONLY FROM DISK=N'AdventureWorks' 
                         WITH NOUNLOAD

There are different ways to get the database internal versions in SQL Server. Following are the different ways:

DATABASEPROPERTYEX:

Returns the current setting of the specified database option or property for the specified database.

Syntax:

DATABASEPROPERTYEX (database, property)

where

database: Is an expression that contains the name of the database for which to return the named property information. database is nvarchar(128).

property: Is an expression that contains the name of the database property to return. property is varchar(128), and can be one of the following values.

In the database parameter, we can pass database that we are interested to find the database internal version.

Property is the version, there are many properties for the database but we are interested to get the version of the database so we can pass the version here.

SELECT DATABASEPROPERTYEX('SQLQUIZ2011', 'version')

DATABASEPROPERTY:

DATABASEPROPERTY returns the named database property value for the specified database and property name. This feature will be removed in the next version of SQL Server.

Syntax:

DATABASEPROPERTY (database , property )

where

database: Is an expression that contains the name of the database for which to return the named property information. database is nvarchar(128).

property: Is an expression that contains the name of the database property to return. property is varchar(128), and can be one of the following values. Property is the version, there are many properties for the database but we are interested to get the version of the database so we can pass the version here.

Using sys.sysdatabases:

Contains one row for each database in an instance of Microsoft SQL Server. When SQL Server is first installed, sysdatabases contains entries for the master, model, msdb, and tempdb databases.

select version, name  from sys.sysdatabases where name = ' SQLQUIZ2011'

DBCC Commands:

Using DBCC DBINFO

DBCC DBCC TRACEON (3604); 
 GO
 DBCC DBINFO
 GO
 DBCC TRACEOFF (3604);

The output The dbiversion shows the current version of the database. You can also tell if the database was created in a previous version of SQL Server with the dbicreateVersion.

Following is the output of DBCC DBINFO command:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBINFO STRUCTURE:


DBINFO @0x0ADEE3E8

dbi_dbid = 11                        dbi_status = 65536                   dbi_nextid = 2121058592
dbi_dbname = SQLQUIZ2011             dbi_maxDbTimestamp = 2000            dbi_version = 655
dbi_createVersion = 655              dbi_ESVersion = 0                    
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 2011-10-21 18:50:28.040
dbi_filegeneration = 0               
dbi_checkptLSN

m_fSeqNo = 37                        m_blockOffset = 409                  m_slotId = 1
dbi_RebuildLogs = 0                  dbi_dbccFlags = 2                    
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                           
dbi_dbbackupLSN

Using DBCC Page command:

DBCC TRACEON (3604); 
GO
DBCC PAGE ('SQLQUIZ2011', 1, 9, 3);
GO
DBCC TRACEOFF (3604);

Restoring database from earlier version of SQL Server to a newer version of SQL Server, we will be able to see the version number in the error log.

If we want to get the internal version information of all the database from the SQL Server instance on the server we can use the following code from the Quiz Master excellent blog.

.-----Script to find DB version for all the databases. 
CREATE TABLE #version (
    VersionID INT
    , VersionName VARCHAR (255)
) 

INSERT #version
    SELECT 515, 'SQL Server 7.0' UNION ALL
    SELECT 539, 'SQL Server 2000' UNION ALL
    SELECT 611, 'SQL Server 2005, VARDECIMAL disabled' UNION ALL
    SELECT 612, 'SQL Server 2005, VARDECIMAL enabled' UNION ALL
    SELECT 655, 'SQL Server 2008' UNION ALL
    SELECT 660, 'SQL Server 2008 R2' 

CREATE TABLE #DBCC (
    ParentObject VARCHAR (255)
    , [Object] VARCHAR (255)
    , Field VARCHAR (255)
    , [Value] VARCHAR (255)
) 

CREATE TABLE #DBCCResults (
    ServerName VARCHAR (255)
    , DBName VARCHAR (255)
    , VersionCreated INT
    , CurrentVersion INT
    , CreatedDate DATETIME
) 

EXEC master.dbo.SP_MSFOREACHDB
    @Command1 = 'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')'
    , @Command2 = 'INSERT INTO #DBCCResults (ServerName, DBName, VersionCreated, CurrentVersion, CreatedDate)
                        SELECT TOP 1 @@SERVERNAME
                        , ''?''
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_createVersion'')
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_version'')
                        , (SELECT Value FROM #DBCC WHERE Field = ''dbi_crdate'')
                        FROM #DBCC'
    , @Command3 = 'TRUNCATE TABLE #DBCC' 

SELECT ServerName
    , DBName
    , v1.VersionName AS VersionCreated
    , v2.VersionName AS CurrentVersion
    , CreatedDate
FROM #DBCCResults dr JOIN #version v1
    ON dr.VersionCreated = v1.VersionID
JOIN #version v2
    ON dr.CurrentVersion = v2.VersionID
DROP TABLE #version, #DBCC, #DBCCRESULTS

Output of the above query looks as shown below and we can get the database version from the CurrentVersion column.

Fig 1: Output of the Above Query showing all database versions on the server.

This is infact a nice question that helped to get the idea of how to get the database internal versions using different options.

http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/

to check version use

Select @@Version