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

By: Editor
March 30, 2010

Page: 1/2

Introduction

As a Database Administrator it is always good to know the history of databases which you manage in your environment. Is there an easy way to identify whether the database was upgraded from a previous version of SQL Server or it was created on the existing instance of SQL Server? In this article we will go through the steps to identify whether a database was upgraded from a previous version of SQL Server or it was created newly on the existing instance.

In this demo, we will first check the database internal version number for Products database which was initially created in SQL Server 2005. Next step will be to restore the Products database on SQL Server 2008 and then check for database internal version number.

"This article has a total of 2 pages including this page. Click the links below to open all 2 pages in a seperate window to learn How to Identify if the database was upgraded from a previous version of SQL Server. Page 1, Page 2"

Identifying Internal Version Number of a Database in SQL Server 2005

Lets connect to SQL Server 2005 using SQL Server Management Studio and execute the below TSQL code to check the internal version number of Products database. Once the Trace FLAG 3604 is enabled, you can execute the undocumented stored procedure DBCC PAGE to retrieve the internal version number of a database.

DBCC TRACEON (3604)
GO

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

DBCC TRACEOFF (3604)
GO

Important Note: - Once you execute a DBCC TRACEON or DBCC TRACEOFF command there will be a corresponding entry added to SQL Server Error Logs.



Once the above code has executed successfully look for dbi_createVersion and dbi_Version values as highlighted in the below snippet. You will be able to see the value for dbi_createVersion as 611 which mean database was initially created on SQL Server 2005 and the value for dbi_Version will also be 611 which mean the database is currently residing in SQL Server 2005 Instance.

SQL Server 2005 Internal Version Number of a Database

Next step will be to restore or attach the Products database on SQL Server 2008 and then verify the internal version number of Products database.


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


Click on Next Page to continue reading rest of the article…


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



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."