Script to Drop and Delete Offline SQL Server Databases and Data Files

Problem

When you use the SQL Server DROP DATABASE command for an offline database it only removes the database records from the SQL Server system tables, but does not remove the actual data and log files from the file system. In this tip we look at a simple and fast tool to drop all of the offline databases from your SQL Server and also delete the MDF, NDF and LDF files of these databases from their respective locations in the file system.

Solution

I have coded a stored procedure called usp_DetachAndDeleteOfflineDBs. The procedure uses the ‘Ole Automation Procedures’ option for the physical file deletion, so this option must be set on for the server configuration.

• The procedures queries the sys.master_files system table joined with sys.databases and gets all the physical file names with file paths of all the offline databases and stores it in a temporary table (#filesToDelete).
• The procedure loops over all the offline databases (state value of 6 in sys.databases) and detaches the databases from the SQL Server system by executing sp_detach_db.
• The procedure loops over all the rows of the temporary tables and uses the sp_OAMethod ole automation procedure with the ‘DeleteFile’ option in order to remove the files from the file system.
• The procedure cleans up the temporary table, closes the cursors and deallocates all objects before finishing.

First we need to enable the ‘Ole Automation Procedures’ configuration option on your server, which can be done as follows:

 EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO

Here is the actual stored procedure to remove the databases.

 use master
go
CREATE PROC usp_DetachAndDeleteOfflineDBs
AS
BEGIN
   DECLARE @filesIndex INT = 1;
   DECLARE @fileCount INT = 0;
   DECLARE @currdb SYSNAME;
   DECLARE @currfile VARCHAR(200)
   DECLARE @Result INT
   DECLARE @FSO_Token INT
   DECLARE c_dbs CURSOR
   FOR
   SELECT db.NAME
   FROM sys.databases db
   WHERE db.STATE = 6;
   SET NOCOUNT ON
   CREATE TABLE #filesToDelete 
     (
       fid INT identity
      ,filepath VARCHAR(200)
      );
   INSERT INTO #filesToDelete
   SELECT mf.physical_name
   FROM sys.databases db
   INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
   WHERE db.STATE = 6;
   SELECT @fileCount = count(*)
   FROM #filesToDelete;
   OPEN c_dbs
   FETCH NEXT FROM c_dbs INTO @currdb
   WHILE @@FETCH_STATUS = 0
   BEGIN
      EXEC sp_detach_db @dbname = @currdb
      FETCH NEXT FROM c_dbs INTO @currdb
   END
   CLOSE c_dbs
   DEALLOCATE c_dbs
   EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
   WHILE @filesIndex <= @fileCount
   BEGIN
      SELECT @currfile = filepath
      FROM #filesToDelete
      WHERE fid = @filesIndex;
      EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @currfile
      SET @filesIndex = @filesIndex + 1;
   END
   EXEC @Result = sp_OADestroy @FSO_Token
   
   DROP TABLE  #filesToDelete
 
   SET NOCOUNT OFF
END
GO

Using the Stored Procedure to Remove Offline Databases and Files

I had two offline databases DB1, DB2 on my server. These databases had four files in the C:\SQL\DB Directory. DB1.mdf, DB1.ldf, DB2.mdf and DB2.ldf

After executing the following statements, databases DB1 and DB2 and their respective mdf and ldf files were removed from my system.

 use master
go
exec dbo.usp_DetachAndDeleteOfflineDBs 
go

Notes

• Test this in a development environment first to make sure you know how this works before using in a production environment.
• This was tested with SQL Server 2012 and 2016 Developer editions.
• The ‘Ole Automation Procedures’ configuration option should be set to true on your server for the above approach to work.
• The ‘xp cmdshell’ configuration option can also be used in order to delete the files from the file system, but I chose to enable only the ‘Ole Automation Procedures’ option since in my opinion opening the command shell from SQL Server creates a greater security vulnerability.

Next Steps

• Modify the process to allow you to select one database instead of dropping all of the offline databases.