This month’s T-SQL Tuesday is being hosted by Glenn Berry , and the subject he chose is “SQL Server 2022.” Microsoft has recently released the RC0 version of SQL Server 2022 and plans for the RTM release in calendar year 2022.
My post for T-SQL Tuesday #154 is going to be a demo of the new SQL Server feature that allows you to restore an Azure SQL Managed Instance backup to a SQL Server 2022 instance. Actually, I am not sure if this is a feature or just an enhancement that allows for this behavior to work.
Current versions of SQL Server do not allow the restoration of backups taken on Azure SQL Managed Instance. Managed Instances are considered “vNext” and runs a different version that is beyond anything on-premises could keep up with due to the frequent changes being applied to Managed Instance. There has always been a disconnect with the restorability between the two platforms, so it is good that Microsoft has found a solution to this limitation.
Below I will walk through a demo of taking a backup from an Azure SQL Managed Instance and restoring that backup to SQL Server 2022 RC0.
Here you can see I have taken a Full Copy Only backup of the AdventureWorks_MI test database I have on an Azure SQL Managed Instance.
Now take a look at my empty SQL Server 2022 installation. See how we are running version 16.0.900.6 with no user databases hosted.
This is my restore script and results. I was able to read the header and view the file list to identify the logical filenames so I can move the files to a correct location on the local file system.
The database upgrade is run automatically for each version between Azure SQL Managed Instance and SQL Server 2022.
Nothing additional was needed to make this functionality work, it just worked.
If you have a need to restore a database from Azure SQL Managed Instance to a full SQL Server instance for whatever reason, with SQL Server 2022 as the destination you now have the ability at your finger tips.
Thank you, Glenn B, for hosting T-SQL Tuesday #154.