How to prevent Page corruption scenarios while restoring SQL DB backup from On-prem to Azure SQL MI

Posted by

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.


While trying to restore the backup of an On-prem DB (That was moved from 1 server to another & Contained memory optimized table) on a managed instance, there could be a possibility of DB corruption if proper precautions are not taken.





The impacted DBs might appear in Restoring state when we login to SSMS as shown below:



If the application is connected to the DB previously, a connection refresh throws the error message shown below:


Msg 64, Level 20, State 0, Line 2
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

 Completion time: 2023-02-13T18:08:14.1270625+04:00



It appears that page corruption happened during the DB restore process that resulted in boot page config failures. As a result, SQL was restarted again and again till the restore requests were cancelled from the back end. While at this stage, one needs assistance from Microsoft support, this can be prevented by the users if they follow a simple process/precaution (Verifying before & after the DB restore) during such on-prem to cloud restore operations.


While doing restore of a backup to an Azure SQL Managed Instance, in-memory OLTP engine asserts during deployment due to an invalid SQL boot page. The backup does not have in-memory OLTP deployed but has invalid in-memory entries in the SQL boot page including non-null in-memory OLTP LSNs. Please restore from a valid backup. If the source database has the inconsistency, it has a corrupted Hekaton state.

To fix/avoid this,

  1. -        create a fresh database
  2. -        verify the Hekaton fields in the bootpage are zeroed out
  3. -        migrate data to new database
  4. -        verify the Hekaton fields in the bootpage are zeroed out
  5. -        take a full backup.
  6. -        restore to SQL MI from this backup"


Steps 2 & 4 above can be carried out by doing the preventive analysis below:

The DB owner performing the restore will need to run the undocumented command



On the fresh database before and after migration to get the Hekaton metadata stored in the SQL bootpage. It is an optional check.


The fresh database will not have Hekaton deployed unless db owner creates an in-memory table in the database.


The output of the above DBCC command will have following Hekaton fields zeroed out:








Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.