Best way to quickly and effectively drop a database and delete all files?

Question

If I set a Microsoft SQL Server database to offline and drop it, it will be deleted, but the log and data files will remain. If I do it in online mode, there is a chance that another connection will stop me from dropping it. In a dev environment, or for a database that I want to periodically blow away and recreate on production, what's the best sequence of T-SQL commands to blow away a database, along with its data files and log files, that will work as long as I have sa privileges and no one is maliciously trying to stop me from dropping the database?

Answer 1

Unless you are in the context of the database, you can't ensure you are the only one using it. Also, don't use batch separators. Run it all as a single batch.

Use AlwaysEncryptedSample;

ALTER DATABASE AlwaysEncryptedSample SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Use master;

DROP DATABASE AlwaysEncryptedSample;

Answer 2

I use the following sequence:

1. Switch to master (or tempdb or any other database besides the one I want to drop)
2. Force the database offline
3. Set it online

4. Drop the database:

   USE master;
   GO
   ALTER DATABASE AlwaysEncryptedSample 
       SET OFFLINE WITH ROLLBACK IMMEDIATE;
   GO
   ALTER DATABASE AlwaysEncryptedSample SET ONLINE;
   GO
   DROP DATABASE AlwaysEncryptedSample;
   GO
   

The problem is if some process is constantly attaching to that database, it becomes a race condition and might not work.