Implicit Database Upgrade and Internal Database Version Numbers

When you run a database from previous version on a SQL Server instance with a higher version, the database is automatically upgraded to the version of the instance. It happens on:

  1. attaching a database from older version;
  2. restroring a database from older version;
  3. and, of course, when you make an in-place upgrade of SQL Server.

For example, when I attach a database from SQL Server 2008 R2 in SQL Server 2014, I’ll get these messages telling about database upgrade.
Converting database 'test' from version 661 to the current version 782.
Database 'test' running the upgrade step from version 661 to version 668.
Database 'test' running the upgrade step from version 668 to version 669.
Database 'test' running the upgrade step from version 781 to version 782.

The version numbers you see in Messages window is internal database version numbers. The list of these numbers can be found at thisĀ post.

Be aware this is a one-direction upgrade. There’s no possibility to downgrade the database nor open it in SQL Server with original version or any intermediate version prior to upgraded one. This means that if your database was created in SQL Server 200, then you opened it in SQL Server 2014, you never can open in SQL Server version from 2005 till 2012. The error message is self-explanatory.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'test' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported.

Another issue is that you can NOT prevent SQL Server from upgrading the database. If you make the primary database file read-only in order to get a read-only database and try to open it in a higher version, you’ll get an error 3415.
Msg 3415, Level 16, State 2, Line 3
Database 'test' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

Leave a Reply

Your email address will not be published. Required fields are marked *