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 the Internal Version number of the 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 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 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 it 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 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 the 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.

Other possible ways of identifying the database's 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.