Eight Ways to Find SQL Server Internal Version Number (SQL Server 2012 Version 706 and SQL Server 2014 Version 782)
Posted: March 10, 2012 Filed under: SQL Server 2008, SQL Server 2012 6 CommentsThe Internal version number of the SQL Server code with which the database was created. It is not a supported value from Microsoft. Future compatibility is not guaranteed but it works for SQL Server 2008 and 2012.
Howerver, many users are coming across this value in error message when they were trying to restore/copy database from a lower version instance. You may not find a good source for documentation about this value. I compiled six ways(T-SQL query) to get this value for your database: the first two are to restore from the the bak file Header and the four other ways are to query online database to get this Internal Version Number.
The quickest way to get this Version value for an online database is to query the sysdatabase:
SELECT name,version FROM sys.sysdatabases
I have added two Powershell methods at the end:
Approach A: Extract this value from backup file Header Infomation:
Method 1. Run the following command from most recent SSMS ( if you run from lower version SSMS, you may not get this value):
RESTORE HEADERONLY FROM DISK=N’c:\backup\sampleDB.bak’
Check these columns from the result panel:DatabaseName,DatabaseVersion,DatabaseCreationDate
The DatabaseVersionison (Internal Version Number) are one of these values:
706(SQLServer 2012), 665/661(SQLServer 2008/R2),611/612(SQLServer 2005/SP2) and 539(SQLServer 2000)
Method 2. Run this script
sp_configure 'show advanced options',1 GO reconfigure Go sp_configure 'Ad Hoc Distributed Queries', 1 GO reconfigure GO SELECT * INTO #myVersion FROM OPENROWSET('SQLNCLI','Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes', 'EXEC(''SET FMTONLY OFF RESTORE HEADERONLY FROM DISK =''''C:\backupserver\sql_backups\mytest1.bak'''''')') AS t SELECT DatabaseName,DatabaseVersion,DatabaseCreationDate FROM #myVersion DROP TABLE #myVersion
Approach B. Database Online methods:
Method 3. Get the version value from DATABASEPROPERTYEX: (DATABASEPROPERTY still works but deprecated)
Run the following query from SSMS for your database:
SELECT SERVERPROPERTY(‘productversion’) as [Product version],
SERVERPROPERTY (‘productlevel’) as [Product level] ,
SERVERPROPERTY (‘edition’) as [Edition],
DATABASEPROPERTYEX((select DB_Name()),’Version’) as [Internal Version Number]
–,DATABASEPROPERTY((select DB_Name()),’Version’) as [Internal Version Number–Deprecated]
,(select DB_Name()) as DBName,
(SELECT COMPATIBILITY_LEVEL FROM SYS.DATABASES WHERE NAME = (select DB_Name())) as compatibilityLevel
Method 4.Retrieve the value from sysdatabase
DECLARE @dbName VARCHAR(128)
SET @dbName=(SELECT Db_name())
SELECT name, version FROM sys.sysdatabases WHERE name = @dbName
Method 5.Use DBCC Command DBINFO (yourdbName)
5.1 Get the values from the result of DBINFO (yourdbName)
You need to turn on undocumented trace flag 3604 to run DBINFO and check the value of dbi_version from the message screen.
DBCC TRACEON (3604)
GO
–get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())
DBCC DBINFO (@dbName)
GO
DBCC TRACEOFF (3604)
GO
There is another value for this database that may be useful to your: dbi_createVersion
You will know the version this database is created from.
5.2 Get these values from a temp table
You can run the following query to get the value in atable:
DBCC TRACEON (3604)
GO
CREATE TABLE #t (ParentObject VARCHAR (255), [Object] VARCHAR (255), Field VARCHAR (500), [Value] VARCHAR (500))
––get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())
insert into #t
exec (‘DBCC DBINFO (‘+@dbName+’) WITH TABLERESULTS’)
GO
select [dbi_dbname],[dbi_version],[dbi_createVersion] from (SELECT Field, Value FROM #t) src
PIVOT (Max(Value) For Field IN ([dbi_dbname],[dbi_version],[dbi_createVersion])) pvt
drop table #t
DBCC TRACEOFF (3604)
GO
5.3 Get these values on the fly:
<code>
EXEC sp_configure ‘show advanced options’,1
GO
reconfigure
Go
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
reconfigure
GO
DBCC TRACEON (3604)
GO
declare @dbName varchar(128) = (select DB_name())
declare @cmd varchar(1000);
select @cmd = ‘SET FMTONLY OFF;SET NOCOUNT ON; EXEC(”” DBCC DBINFO (‘+@dbName+’) WITH TABLERESULTS””)’;
EXEC(‘SELECT * INTO myVersion from OPENROWSET(”SQLNCLI”,”Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes;”,”’ + @cmd + ”’) AS t;’ )
SELECT Field, Value FROM myVersion
WHERE Field =’dbi_createVersion’ or Field =’dbi_crdate’ or Field =’dbi_Version’ or Field =’dbi_dbname’
DROP TABLE myVersion;
DBCC TRACEOff (3604)
GO
</code>
Method 6: Use DBCC Command PAGE (yourdbName,1,9,3)
6.1 Get the value from the result of PAGE (yourdbName,1,9,3)
DBCC TRACEON (3604)
GO
–get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())
DBCC PAGE (@dbName, 1, 9, 3);
GO
DBCC TRACEOFF (3604)
GO
You can get the same information(dbi_version and dbi_createVersion) for the database from this DBCC command.
6.2 Get the values from a temp table
You can run another query to get the values in a table:
DBCC TRACEON (3604)
GO
CREATE TABLE #t (ParentObject VARCHAR (255), [Object] VARCHAR (255), Field VARCHAR (255), [Value] VARCHAR (255))
––get your database name
declare @dbName varchar(128)
Set @dbName=(select DB_name())
insert into #t
exec (‘DBCC PAGE(‘+@dbName+’,1,9,3) WITH TABLERESULTS’)
GO
select [dbi_dbname],[dbi_version],[dbi_createVersion] from (SELECT Field, Value FROM #t) src
PIVOT (Max(Value) For Field IN ([dbi_dbname],[dbi_version],[dbi_createVersion])) pvt
–select * from #t
drop table #t
DBCC TRACEOFF (3604)
GO
6.3 Get the values on the fly:
EXEC sp_configure ‘show advanced options’,1
GO
reconfigure
Go
EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
reconfigure
GO
DBCC TRACEON (3604)
GO
declare @dbName varchar(128) = (select DB_name())
declare @cmd varchar(1000);
select @cmd = ‘SET FMTONLY OFF;SET NOCOUNT ON; EXEC(”” DBCC PAGE (‘+@dbName+’, 1,9,3) WITH TABLERESULTS””)’;
EXEC(‘SELECT * INTO myVersion from OPENROWSET(”SQLNCLI”,”Server=USWDM2ML5054498\SQL08R2;Trusted_Connection=yes;”,”’ + @cmd + ”’) AS t;’ )
SELECT Field, Value FROM myVersion
WHERE Field =’dbi_createVersion’ or Field =’dbi_crdate’ or Field =’dbi_Version’ or Field =’dbi_dbname’
DROP TABLE myVersion;
DBCC TRACEOff (3604)
GO
Paul Randal has an valuable information page for this undocumented DBCC PAGE command at this link:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx
The syntax: dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Here is a short list of version for the major SQL Server versions I compiled:
SQL Server Version | Compatibility Level | Internal Database Version | Build |
SQL Server 2012 RTM | 110 | 706 | 11.0.2100.60 |
SQL Server 2008 R2 | 100 | 665 | 10.50.xxxx |
SQL Server 2008 | 100 | 661 | 10.00.xxxx |
SQL Server 2005 SP2 | 90 | 612 | 9.00.xxxx |
SQL Server 2005 | 90 | 611 | 9.00.xxxx |
SQL Server 2000 | 80 | 539 | 8.00.xxx(x) |
There is a rule here you have to follow: you can move up yourdatabase to newer version but not reverse the order.
If you want to know all database versions on your server, you can try this script from Sankar Reddy: http://sqlserverpedia.com/blog/sql-server-bloggers/database-internal-version-create-version-and-current-version/
You may be interested to read Jonathan Kehayias’ blog post to know more background information: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx
Another source with many responses:
Approach C with Powershell:
Method 7: Another way is to use Powershell (Run the following within SSMS 2014) and my DB returns: 782 for SQL Server 2014 RTM.
$srcinst='MC\MSSQL2014' $srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srcinst $srv.Databases.Item("myDB").Version
Method 8:
Import-Module -Name 'SQLPS' -DisableNameChecking $svrname = "MC\MSSQL2014" $mydbName="*" # all databases $myTablePath= "SQLSERVER:\SQL\$svrname\Databases" Get-ChildItem $myTablePath | ?{$_.name -Like $mydbName} | Select-Object Name,Version | Format-Table –AutoSize
[…] ← Six Ways to Find SQL Server Internal Version Number (SQL Server 2012 Version 706) It’s time to backup DNN database with compression.( SQL Server 2008, 2012) […]
very useful post … sql dba
Whatever happened to @@Version ?????? Microsoft is driving us NUTS!!!
i personally, have 3 versions of sql server installed:
1. sql2005 (version 9.0.5000):
if i create a new db in this version of sql, then detach the database files it created using default sql settings, i then analyze the file with my utility, i get value 611. this value is in agreement with the value you publish here on this page.
2. sql2008r2 (version 10.50.4000):
if i create a new db in this version of sql, then detach the database files it created using default sql settings, i then analyze the file with my utility, i get value 661. this value is disputed. don says this version should be 665. however for me, it is 661. this discrepancy needs to be explained ???
3. sql2012 (version 11.0.3128):
if i create a new db in this version of sql, then detach the database files it created using default sql settings, i then analyze the file with my utility, i get value 706. this value is in agreement with the value you publish here on this page.
(4. while i dont have sql2000 installed, a friend who does, created a db using msde, then detached the database files it created using default sql settings, i then analyze the file with my utility, i get value 539.. this value is in agreement with the value you publish here on this page.)
so it seems there is some question to resolve about version number 661. your page states this is for sql2008, but i get value 661 for databases created using sql server 2008r2. this is a discrepancy.
might anyone have an answer to explain this?
greg aiken
You can check this thread to see the issue you mentioned here:
http://social.msdn.microsoft.com/Forums/en-US/3691f370-d434-4742-9ab6-c4fb88c968dd/sql-version-661-and-665
Thank you sooo much. Very helpdful!