This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Restoring a backup file is the easiest way to copy a SQL Server database to another instance. It allows you to create a copy of your production database for easier troubleshooting or debugging of an issue, to provide a copy of a database to your end users or eligible third parties, or as a light-weight business continuity/disaster recovery solution to restore functionality on another instance of SQL Server. These are just a few use cases, and the list is much longer and there are some very inventive ways of using backup-restore in the wild.
Why a backup from a newer release of SQL Server cannot be restored to an older release
The challenge with restoring backups is that earlier version of SQL engine cannot understand the internal format of backup file taken from the SQL Server instance with the newer version of SQL engine. There are new internal structures, new fields in the existing internal structures, or new values for the existing fields that older version of SQL engine simply doesn’t know how to deal with. For example, this is the error message you will get when you try to restore a backup file taken on SQL Server 2017 to an instance of SQL Server 2016:
| The database was backed up on a server running version 14.0.1000.169. That version is incompatible with this server, which is running version 13.00.1601.5 |
Minor version numbers in the error message may vary based on the cumulative update (CU) and/or Service Pack (SP) installed on the instance, but the outcome will be the same. The same will happen with any other two releases of SQL Server if source instance is on the higher release and hence major version number than the destination instance. Note though that having two servers with the same release, say SQL Server 2016 marked with major version number 13, and different CUs meaning different minor version numbers, will not result in the error message – you will be able to perform restore of backup file.
What about restoring backups from Azure SQL Managed Instance
The same happens today with Azure SQL Managed Instance as a source and any release of SQL Server as a destination. That’s because Managed Instance service is being upgraded almost continuously and most of the time it contains newly introduced SQL engine features and newer internal format compared to the latest available release of SQL Server. There are alternative mechanisms to copy or move a managed instance database described in the earlier blog post, but backup-restore is still the most comfortable way and one of the top voted features for Azure SQL Managed Instance service on Azure Feedback at the time of writing this article.
The introduction of a new major release of SQL Server happens once in few years and it is the time when the internal SQL engine versions of Managed Instance and SQL Server are closest or even identical. The latest such an event happened very recently with SQL Server 2022 public preview announced back in May. That opens an opportunity to align the SQL engine version of a managed instance with SQL Server 2022 engine version and keep them close enough as time goes by so that backup-restore can work in both directions. Keeping the versions harmonized requires some extra steps on the service side, which means that particular managed instance should be marked appropriately so that its version doesn’t diverge irrevocably through the service upgrades.
Announcing private preview
We’re happy to announce that the option of restoring a full copy-only backup taken from Azure SQL managed instance to instance of SQL Server 2022 is now available for private preview. It currently requires a new test instance to be created by following specific instructions.
The purpose of the private preview is to validate that the functionality meets your needs. Taking a copy-only backup and restoring it is a rather simple procedure, but we are interested in your feedback on the overall experience, handling of Transparent Data Encryption (TDE) data protection at rest, or any edge cases specific to your database and workload that you may come up with. To claim that database is successfully copied or moved, it’s not enough to restore the database backup without any error message. It’s crucial that your application/solution continues operating as expected once repointed to the restored database(s).
If you are interested in trying out the functionality, please review the private preview onboarding instructions to learn more and sign up for the preview.