The importance of SQL Serverís internal database version
Moving databases between servers can be problematic even when those servers are running the same major version of SQL Server. See how you can prevent compatibility issues when trying to restore database from one instance to another by examining the internal database version number.
It is useful to know what internal database version a backup was created from and what version you're trying to restore to. The internal database version is basically a marker to signify the internal file format for the database, and this can change when, for example, additional storage engine features are added. This typically only happens between major releases, but has happened inside a major release as well.
When you back up a database from an older version and restore it on a newer version, the file format is upgraded as part of the restore process. You may have seen these types of error messages in the output or in the errorlog:
spid24s Converting database 'dbname' from version 852 to the current version 904.
spid26s Database 'dbname' running the upgrade step from version 852 to version 853.
spid26s Database 'dbname' running the upgrade step from version 903 to version 904.
RESTORE DATABASE successfully processed …
List of Major SQL Server Versions
Here is a table showing the list of major SQL Server versions and their corresponding internal database versions you're most likely to see, going back to SQL Server 2000. (If you see an internal database version number in the wild not represented here, then it is almost certainly from a pre-release version.)
|Internal Version||Major Version||Notes|
|539||SQL Server 2000|
|611||SQL Server 2005|
|612||SQL Server 2005 SP2||The file format changed when VARDECIMAL was added. This meant you couldn't restore a 2005 SP2 backup to 2005 SP1, even if you weren't using the feature.|
|655||SQL Server 2008|
|661||SQL Server 2008 R2||I've seen reports of 660 and 662, but haven't observed directly. And I've seen at least one thread that talks about 665 but I think this was just a typo (they meant 655).|
|706||SQL Server 2012|
|782||SQL Server 2014|
|852||SQL Server 2016|
|869||SQL Server 2017||I don't have any evidence, but I believe when SQL Server 2017 was first released it had an internal version of 868. RTM from current media shows 869.|
|904||SQL Server 2019||The latest Cumulative Update (#16) still has the same internal version as RTM.|
|> 904||SQL Server 2022||This version isn't released yet but assume for now that any version higher than 904 is from a pre-release version.|
This list is important; if you are unable to perform a restore, you can use it to quickly determine the versions involved. A restore may fail for one of the following reasons.
The target server is too low
If the target server has an internal database version lower than the database backup, there is simply no way to make it work. You can't go backwards.
Let's say you took a backup from SQL Server 2017 and are trying to restore it to a SQL Server 2016 instance, you will see this error message:
Could not open new database 'dbname'. CREATE DATABASE is aborted.
The database 'dbname' cannot be opened because it is version 869. This server supports version 852 and earlier. A downgrade path is not supported.
This is the case even if you aren't using any features or options that are available only in the new version. What you'll have to do instead is either upgrade the target instance to the newer version, or migrate the schema and data via other, non-backup methods (such as the Generate Scripts wizard in SSMS).
The target server is too high
You might try to restore a backup that came from an ancient version, like SQL
Server 2000. In this case, there have been too many changes to the file format,
and more modern versions simply won't support the conversion. This is reported
with a slightly different error message (3169), which includes parts of
@@VERSION instead of internal database versions, but
the concept is the same:
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 15.00.4223. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
As a workaround, you can use the same Generate Scripts wizard mentioned above by connecting a newer version of Management Studio to the older instance (assuming the database is still attached there). A more typical workaround is to restore the 2000 backup to another instance first (say, SQL Server 2008), then back that database up and restore that on the newer version. This can be cumbersome if you don't already have an in-between instance to use, but it is arguably easier to stand one up temporarily than to go through the wizard.
The backup came from Managed Instance
You may not know that your backup came from Azure SQL Managed Instance, and the error message will be confusing because the major versions are the same and the source is lower (which usually works):
The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.4223. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
I know just enough about Managed Instance to know that the workaround here is to use a dacpac (example here).
How to determine internal database version
If you are preparing for a database migration, you can check that the versions will be compatible by comparing the internal database version on both sides:
If you have a backup and the original database is not online or not accessible,
you will need to pull info from the .bak file using the
RESTORE HEADERONLY FROM DISK = '…path…\BackupFile.bak';
The columns of interest here will be
If you just have an MDF file, I'm sure there is a way to get at the internal
database version with a hex editor, but it is likely much simpler to just (try to)
attach a copy of it to an instance. You'll either see the
source version in an error message if the attach fails, or you'll be able
to see the source version from the
If you are planning to migrate databases from one SQL Server instance to another, you should make sure you are comfortable with the different versions involved. You can prevent any issues by making sure the backup will be restored to a server running an equal or higher internal database version.
See these tips and other resources:
- How to migrate a SQL Server database to a lower version
- Why Can't I Restore a Database to an Older Version of SQL Server?
- Upgrading SQL Server databases and changing compatibility levels
- Migration SQL Server 2000 to SQL Server 2012
- Eight Ways to Find SQL Server Internal Version Number
About the author
View all my tips
Article Last Updated: 2022-06-01