Why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

By: Editor
April 02, 2010

Introduction

If you are trying to restore a database on an SQL Server 2005 or a lower version from the database backup which was performed on an SQL Server 2008 instance then it will fail. In this article we will take a look at the reason why an SQL Server Database from a higher version cannot be restored onto a lower version of SQL Server?

Error Message received when restoring an SQL Server 2008 database backup on an SQL Server 2005 or lower versions

Msg 3241, Level 16, State 7, Line 1
The media family on device 'Drive:\BackupFolder\DatabaseName.BAK' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The reason for the failure is the internal version number of SQL Server 2008 is different from that of an SQL Server 2005 or lower versions. Since the internal version number of SQL Server 2008 is higher than that of an SQL Server 2005 database the restore process fails. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.

Important Note: - You will not be able to restore a database backup from a higher version of SQL Server to a lower version of SQL Server this is by design from Microsoft. Downgrade of a database is not supported using the database backup and restore method.



Why an SQL Server Database from a higher version cannot be attached onto a lower version of SQL Server?

If you try to attach an SQL Server database which was initially created on an SQL Server 2008 instance to an SQL Server 2005 or a lower version then the process will fail with the below mentioned error.

Error Message received when attaching an SQL Server 2008 database on to an SQL Server 2005 or lower versions

Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'DatabaseName' cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.

The above error message clearly states that the database cannot be opened in SQL Server 2005 instance as the database which you are trying to attach is of the version 655. The SQL Server 2005 instance can only open databases which are of version 611 or earlier. To know more about internal version number of a database checks the following article How to identify if the database was upgraded from a previous version of SQL Server.

In below table you will able to relate Database Compatibility Levels and Internal Database Version Numbers for different versions of SQL Server.

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
SQL Server 2012 110 706

Conclusion

In this article you have seen why you can’t restore or attach an SQL Server database from an higher version of SQL Server to a lower version.

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

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