#0311 – SQL Server – Database files upgraded from version nnnn to mmm – why databases cannot be restored backwards?


Recently, we were upgrading our SQL Server instances to the latest cumulative upgrades from Microsoft. All our development and QA environments were upgraded. After a couple of days, I received the following query from one of the interns:



I’m restoring the backup of an existing database. During the restore, the Results pane in the SQL Server Management Studio displays the following message. Am I doing anything wrong?


Converting database ‘FileVersionUpgrade’ from version 661 to the current version 706.
Database ‘FileVersionUpgrade’ running the upgrade step from version 661 to version 668.
…..
Database ‘FileVersionUpgrade’ running the upgrade step from version 705 to version 706.


This message is perfectly normal and simply indicates that the database being restored was backed up in a version of SQL Server that has a major version (e.g. major version of SQL Server 2008 is 10, that of SQL 2012 is 11 and so on) different from the one to which it is being restored or is on a different patch level.


In fact, the database files have an underlying  structure that is unique to the version of SQL Server (major versions may also have major changes to the file structure). When a database is restored, SQL Server upgrades the underlying file structure step-by-step from the supported source version to the destination version – which are the informational messages that are seen on the SSMS Messages tab.


Unfortunately, no documentation exists on Books On Line that would provide a mapping between the file version number and the corresponding SQL Server version. One can build such a table by running the following query against the master database for each SQL Server version:

DBCC DBINFO(‘master’) WITH TABLERESULTS

The upgrade in the internal file structure is also the reason why a database backup taken on a newer release/patch level of SQL Server cannot be restored on a previous release/patch level of SQL Server.


As closing notes, please note that:



  • This is seen during attach and restore operations both

  • These file versions are the internal storage format, and have nothing to do with the compatibility level of the database

Further Reading:



  • DBCC DBINFO WTIH TABLERESULTS – In which SQL Server version was this database created? [Link]
  • Restore databases from a backup file across a network [Link]

Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s