How to identify if the database was upgraded from a previous version of SQL Server

By: Editor
March 30, 2010

Page: 2/2  

Identifying Internal Version Number of a Database in SQL Server 2008

Once you have restored / attached the Products database on SQL Server 2008 instance and then execute the below mentioned DBCC commands to verify the internal version number of the Products database when it now residing in SQL Server 2008 instance.

DBCC TRACEON (3604)
GO

DBCC PAGE ('Products',1,9,3)
GO

DBCC TRACEOFF (3604)
GO

SQL Server 2008 Internal Version Number of a Database   

Carefully look for dbi_createVersion and dbi_Version values in the above snippet. You will be able to see the value for dbi_createVersion as 611 which inform you that the database was initially created on a SQL Server 2005 instance. Next look for the value dbi_Version which will be 655 it means that the database is currently residing in SQL Server 2008 Instance. This way you will be able to understand that the database was upgraded to SQL Server 2008 and was not initially created on an SQL Server 2008 instance.



Important Note: - You can identify whether the SQL Server Instance was installed fresh on the server or an In-Place upgraded was performed from SQL Server 2000 or SQL Server 2005 to SQL Server 2008 by checking the value for dbi_createVersion and dbi_Version properties.

In below table you will able to relate database compatibility levels and internal database version numbers for different versions of SQL Server. To know more about different SQL Server Build Versions check the following Microsoft SQL Server Build Versions article. 

SQL Server Edition Database Compatibility Level Internal Database Version Number
SQL Server 7 70 515
SQL Server 2000 80 539
SQL Server 2005 90 611/612
SQL Server 2008 100 655
SQL Server 2008 R2 105 660

Conclusion

In this article you have seen how easily a database administrator can identify whether the database was upgraded from a previous version of SQL Server or it was created on the same instance.


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Previous Page.. How to identify if the database was upgraded from a previous version of SQL Server


Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."