The importance of SQL Serverís internal database version

By:   |   Updated: 2022-06-01   |   Comments   |   Related: More > 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.)

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:

Msg 1813, Level 16, State 2
Could not open new database 'dbname'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1
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:

Msg 3169, Level 16, State 1
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):

Msg 3169, Level 16, State 1
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:

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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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.

View all my tips


Article Last Updated: 2022-06-01

Comments For This Article





download














get free sql tips
agree to terms