A few months ago, one of my customers asked me to create a new DEV environment. The interesting part of the project was the request for self-renewing databases. Those databases had to renew themselves with a few clicks from an Azure SQL managed instance. Kind of self-auto-renewing.
The final script was long and pretty complicated. It contained point-in-time restore (PITR), decryption of transparent data encryption (TDE), data masking, and so on. The last stage of the work is a backup, transfer, and restore of the database to the on-prem instance. SQL Server 2022 has been installed on the on-prem server, due to Microsoft's promise of compatibility with Azure SQL MI. That allowed us not to use sqlpackage and reduce deploy process time.
Today, the process has stopped working.... after some investigation, we found that the process failed with the next message:
The database was backed up on a server running database version 957. That version is incompatible with this server, which supports version 950.
Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.Ok, the script tried to restore a new version of the DB to the older one, that's quite understandable from the message. But what is version 950 or 975, and why it's has changed?
Follow me: there is no way to attach or restore a database created using a recent version of SQL Server to an older one. The main reason for this restriction is due to physical file changes in a more recent release. The older version simply cannot "know" about those changes.
If you attempt to do it, you will get an error with the internal version numbers listed in the error message text. This is an internal database version. We can call this the "physical" version if you want.
Unfortunately, the Microsoft documentation is not clear about the internal SQL Server database versions. The official information is:
Internal version number of the SQL Server code with which the database was created. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
One of the following methods can be used to verify an internal database version:
-- Using system compatibility view
USE [master]
GO
SELECT
[name],
[version]
FROM
dbo.sysdatabases;
GO
-- Search for dbi_version or dbi_createVersion, using DBCC PAGE to look at the boot page (9) of the database
DBCC TRACEON (3604);
DBCC PAGE('MyDB',1,9,3) WITH TABLERESULTS;
DBCC TRACEOFF (3604);
GO
-- Search for dbi_version or dbi_createVersion, using DBCC DBINFO
USE [MyDB]
GO
DBCC TRACEON (3604);
DBCC DBINFO WITH TABLERESULTS;
DBCC TRACEOFF (3604);
GO
-- Using a status column and find "Version= "
EXEC sp_helpdb;
-- Using a restore headeronly statement
RESTORE HEADERONLY FROM DISK = '…\YourBackupFile.bak';
-- Using Metadata function
SELECT DATABASEPROPERTYEX(N'MyDB', 'Version');
GOThe current full known list of the internal database version:
So, back to my customer story. From the versions table, we learn, that the version numbers are numbers of SQL Server 2022 RC1 & RTM. A few months ago, when we created this instance, the RTM version did not exist yet. A new release came with a new internal database version and that caused an issue.
Because RC1 (Release Candidate) is not really a fully functional version and the RTM one is the first official one, we didn't have a build version to fix this problem. We have downloaded a new SQL Server 2022 RTM version and performed an in-place upgrade. Simply by the Installation Center.
I've explained to the customer that to avoid this in the future, we probably need to change our process or use a newly realized feature of restoring a SQL Managed Instance database backup to SQL Server 2022. For now, he chose to stay with the current solution.... :)
More reading:
Comments