Know The History Of Your SQL Server Database

Introduction

As an administrator it is critical to know the history of the database that you manage in your environment. This article proposes the procedure to identify which version of SQL Server the database was built.

For Example: Whether the database was built on the previous version of SQL Server or if it was created on the existing instance of SQL Server.

Identifying Internal Version number of database

We will now connect to SQL Server 2008 using SQL Server Management studio and execute the following T-SQL Statements.

DBCC TRACEON (3604)

GO

 

DBCC PAGE ('161268DB',1,9,3)

GO

 

DBCC TRACEOFF (3604)

GO

The "TRACEON (3604)" flag helps us to execute the undocumented stored procedure DBCC PAGE to retrieve the internal version number of the database.

Once you have successfully executed the preceding T-SQL statements, kindly search for the dbi_createVersion and dbi_version values in the in the messages pane. Then in the following picture you will be able to see the value for "dbi_createVersion" as "611" and "dbi_Version" value as "611".



If the "dbi_createVersion" value is 611 then that means that the database was created on SQL Server 2005. And if the "dbi_Version" value is 611 then means that the database is currently residing in SQL Server 2005.

Note: Once you have executed the DBCC TRACEON and DBCC TRACEOFF commands, the corresponding entry will be added to SQL Server error logs.

Now, when executing the following T-SQL statements in SQL Server 2008 and looking for the dbi_createVersion and dbi_version values in the in the messages pane, in the following picture you can see the value for dbi_createVersion as "611" and dbi_Version value as "655".



If the dbi_createVersion value is 611 then that means that the database was created on SQL Server 2005. And if dbi_Version value is 655 then that means the database is currently residing in SQL Server 2008.

From this you can clearly understand that the database was originally created on SQL Server 2005, then the database was upgraded to SQL Server 2008 and now it is residing in SQL Server 2008.

The following table will help us to understand the internal database version numbers of the various versions of SQL Server.

S.No	SQL Server Version	Internal Database Version Number
1	SQL Server 7	515
2	SQL Server 2000	539
3	SQL Server 2005	611/612
4	SQL Server 2008	655
5	SQL Server 2008 R2	660

           

Other possible ways of identifying the database Internal Version Number are:

1. SelectDATABASEPROPERTYEX('161268DB','Version')
   
   
   
   
2. SelectDATABASEPROPERTY('161268DB','Version')
   
   
   
   
3. Select name, versionfrom sys.sysdatabaseswhere name ='161268DB'
   
   
   
   
4. Code with output:
   
   
   

Conclusion

This article proposes various ways for a database administrator to determine whether the database was upgraded from an earlier version of SQL Server or was created on the same instance.

Reference(s)

• http://www.mytechmantra.com/LearnSQLServer/