1

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?

2

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.

1
  • Thanks @AMtwo. I had suspected as much since I found a page (for Azure SQL Databases) that had a note: "You cannot overwrite an existing database during restore." And I know managed instances are built around Azure SQL Databases. – PatrickSteele Oct 12 '18 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.