How do I disconnect open connections to an Azure SQL Managed Instance?

Question

I am working on a process for restoring a database to my Azure SQL Managed instance which involves dropping the existing database and restoring a backup in its place (since Managed Instance doesn't support WITH REPLACE).

I am, however, running into an issue with disconnecting any open connections so I can do this operation. Users should be warned before updates take place, but we cannot guarantee no open connections.

Typically, I would do something along the lines of the following:

ALTER DATABASE AdventureWorks2012 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--RESTORE HERE
ALTER DATABASE AdventureWorks2012 SET MULTI_USER;

When I attempt to run something like this against the database in the managed instance, however, I get the following error:

This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again. ALTER DATABASE statement failed.

Is there a way to accomplish this in an Azure SQL Managed Instance?

Answer 1

In RESTORE FROM URL in Managed Instance you can't even replace existing databases.

So either drop or rename the database before the RESTORE. Both DROP DATABASE MyDb and ALTER DATABASE MyDb MODIFY NAME = MyDb_old will kill existing connections to the database.

Alternatively you can use the Managed Point-in-Time Restore to restore an existing database to a previous point-in-time.