Sql-server – Restore DIfferential backup on azure managed instance

azure-sql-databaseazure-sql-managed-instancesql server

I am migrating my database from on-premise to Azure managed instance. In order to move the database I'm using the organization weekly full backup, and I am asking them to take a diff after they take the application offline (the databases are very large).

Question: Can I restore a differential backup on azure managed instance?

When I try to restore the full with norecovery than apply the DIFF. I get an error: "restore with norecovery is not a supported option in azure managed instance"

Best Answer

Question: Can I restore a differential backup on azure managed instance?

No. Borrowing from my answer here, Managed Instance only supports the restore of full backups; no log or diff. See this thread:

Lesson Learned #82: Azure SQL Database Managed Instance supports only COPY_ONLY restoring a database backup

And the limited syntax diagram for RESTORE in the documentation for Managed Instance:

RESTORE Statements (Transact-SQL)

The docs imply that RESTORE ... FROM URL will implicitly add WITH RECOVERY and so there will be no opportunity for logs or diffs to be applied after the initial restore. So your downtime will be >= the time it takes to take a COPY_ONLY full backup, put it somewhere MI can access, restore it, and re-point your app(s). You might also want to read through this tutorial, for a different approach.

Related Solutions

Sql-server – Migrating differential backup restoration

Welcome to the forum. You do not need to do log restores between a FULL and a DIFF backup in SQL Server. If you do you'll break the LSN (Log Sequence Number) used to mark the delta's between the restores. A differential backup is all the transactions from the last full backup. SQL Server notes the last full backup in a data page when it backs up so it knows what data pages have changed. At that point any DIFF backup from the last FULL backup will work.

Note the newer versions of SQL Server have a 'COPY ONLY' option which will NOT affect your LSN or modified pages since last FULL backup. So basically don't use COPY ONLY.

Finally you'll want to do a restore with NORECOVERY. I know this sounds counter intuitive at first but basically you're telling the engine do not play back the log files.

--This restores the full DB but does not replay the open transactions at the time, leaving it consistent for a DIFF or transaction log restore
RESTORE DATABASE DATABASE FROM '<pathofDBBackup>' WITH NORECOVERY;

--This is your DIFF restore and at the end you can recover.
RESTORE DATABASE DATABASE FROM '<pathofDIFFDBBackup>' WITH RECOVERY;

In case you needed more info:

Understanding How Restore and Recovery of Backups Work in SQL Server

The excellent backup internals videos by Paul Randall.

Backup Internals Demo

Sql-server – Replicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed Instance

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following: "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.

Best Answer

Related Solutions

Sql-server – Migrating differential backup restoration

Sql-server – Replicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed Instance

Related Question