Azure SQL – Cannot Overwrite Existing Managed Instance Database During Restore

azuresql server

We're working on migrating some SQL Server VMs to Azure SQL Managed Instances. We did an initial backup/restore without a problem:

RESTORE DATABASE [xxx] 
    FROM URL = 'https://<account-name>.blob.core.windows.net/<container-name>/<filename>.bak'

After doing testing, we now need to do a fresh restore from our SQL VMs. We started doing the same process we initially did but when restoring, we got an error:

Msg 1801, Level 16, State 1, Line 1
Database 'xxx' already exists. Choose a different database name.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

So I tried the WITH REPLACE option. Unfortunately:

Msg 41901, Level 16, State 2, Line 1
One or more of the options (replace) are not supported for this statement in SQL Database Managed Instance. Review the documentation for supported options.

Reviewing the documentation just tells me that WITH REPLACE isn't available on a Managed Instance. I can't find any documentation on doing an in-place restore/overwrite of an existing managed instance database.

Am I forced to drop and re-create the database?

Best Answer

Yes, you will have to DROP the prior database before RESTORE as the REPLACE syntax isn't supported on Managed Instance.

Doing a RESTORE on Managed Instance (MI) will do some other work behind the scenes, too. In the General Purpose tier, you get one Azure Premium disk per data file, and that is handled completely by MI. This manifests in theRESTORE syntax by not allowing you to use MOVE (because the service essentially does that for you).

REPLACE is similarly disallowed right now, and I suspect it's also related to the magic of allocating storage and moving files on restore.

Related Question