From the docs, it would seem that restoring a full database backup file is possible, but can one also restore transaction logs?
(the idea is to minimize downtime in a migration from on-prem to MI).
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
No, log shipping to Managed Instance is not possible; it only supports the restore of full backups. See this thread:
And the limited syntax diagram for
RESTORE in the documentation for Managed Instance:
The docs imply that
RESTORE ... FROM URL will implicitly add
WITH RECOVERY and so there will be no opportunity for logs 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.
We have now (Feb 2021) enabled log shipping as a part of an external service called Log Replay Service (LRS). The reason we could not enable log shipping directly from T-SQL was due to security concerns. The LRS service when invoked will execute the log-shipping in NORECOVERY mode behind the scenes on Managed Instance. More about this release here: https://aka.ms/mi-logreplay-service. Hope this helps. -Dani from the SQL Managed Instance Product Group