Sql-server – Best way to quickly and effectively drop a database and delete all files

sql servert-sql

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?

Best Answer

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;