The importance of SQL Server’s internal database version

By: Aaron Bertrand   |   Updated: 2022-06-01   |   Comments   |   Related: > Restore

Problem

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.

Solution

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

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:

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:

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):

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:

SELECT @@VERSION, 
  SERVERPROPERTY('ProductMajorVersion'), 
  DATABASEPROPERTYEX(N'master', 'Version');

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 command:

RESTORE HEADERONLY FROM DISK = '…path…\BackupFile.bak';

The columns of interest here will be DatabaseVersion, SoftwareVersionMajor, SoftwareVersionBuild, and CompatibilityLevel.

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 Converting… message.

Next Steps

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

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips