How to identify if the database was upgraded from a previous version of SQL Server
March 30, 2010
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.
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|
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...
Geeks who read this article also read…
- How to Downgrade SQL Server Database from a higher version to a lower version
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- Enforce Password Policies and Password Expiration for SQL Server Logins
- Microsoft SQL Server Build Versions
- Auditing SQL Server Logins
- Database Backup Compression Feature In SQL Server 2008
- Read More SQL Server Articles…