This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Can’t overwrite an existing Azure SQL Managed Instance database during restore
Question
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.
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.
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?
asked 2018-10-12 by PatrickSteele
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.
answered 2018-10-12 by Andy Mallon