Delete Multiple Databases

_{Saturday, January 2, 2016 5:24 AM}

Hi,

In my SQL Server 2008, there are a lot of databases like this:

'MyDatabase2015-02-11'

'MyDatabase2015-05-07'

...

I want to delete all of them, can I use wild char like DOS command:

DROP DATABASE 'MyDatabase*';

to remove all these databases? I try but fail.

_{Saturday, January 2, 2016 5:38 AM ✅Answered}

Hello,

Please read the following URL:

http://stackoverflow.com/questions/3101399/drop-database-in-sql-server-using-wildcard

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

_{Saturday, January 2, 2016 6:02 AM}

Hi ,

Take imp database backup in instance.

https://deangrant.wordpress.com/2013/05/21/delete-multiple-databases-in-sql-service-using-a-filter/

Please click Mark As Answer if my post helped.

_{Saturday, January 2, 2016 6:27 AM}

Try the below script:

The below uses retry method which can be helpful for online dbs

SET NOCOUNT ON

DECLARE @DBSTODELETE NVARCHAR(MAX)  
DECLARE @SQL NVARCHAR(MAX)
DECLARE @START INT = 1
DECLARE @END INT 
DECLARE @DBNAME NVARCHAR(50)
DECLARE @COUNTER_START SMALLINT = 1
DECLARE @COUNTER_END SMALLINT = 5
DECLARE @PhysicalPath_mdf NVARCHAR(100)
DECLARE @PhysicalPath_ldf NVARCHAR(100)
DECLARE @cmdscript NVARCHAR(100)

IF OBJECT_ID ( 'TEMPDB..#DELETEDBS' ) IS NOT NULL DROP TABLE #DELETEDBS
CREATE TABLE #DELETEDBS ( ID INT IDENTITY(1,1), DBNAME NVARCHAR(100))

/* ASSIGN VALUES STARTS HERE */
SELECT @DBSTODELETE = 'test1,test2' -- COMMA SEPARATED CLIENT DBNAMES TO DELETE
/* ASSIGN VALUES ENDS HERE */

SELECT @SQL = 'SELECT ''' + REPLACE ( @DBSTODELETE, ',', ''' UNION ALL SELECT ''') + ''''
INSERT INTO #DELETEDBS (DBNAME) EXEC(@SQL)

SELECT @END = MAX(ID) FROM #DELETEDBS

WHILE @START <= @END
BEGIN 
    SELECT @DBNAME = DBNAME FROM #DELETEDBS WHERE ID = @START
    
    WHILE @COUNTER_START <= @COUNTER_END
    BEGIN
    BEGIN TRY

            SELECT  @PhysicalPath_mdf = Case when type = 0  Then physical_name Else @PhysicalPath_mdf end,
                        @PhysicalPath_ldf = Case when type = 1  Then physical_name Else @PhysicalPath_ldf end
             FROM sys.master_files A
                Inner Join sys.databases B on A.database_id = B.database_id where b.name = @DBNAME

            IF EXISTS ( SELECT 1 FROM SYS.DATABASES  WHERE NAME = @DBNAME )
            BEGIN
            SELECT 'Deleting '+ @DBNAME
    
            EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = @DBNAME
        
            EXEC ('ALTER DATABASE ['+@DBNAME + '] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE' )
            EXEC ('ALTER DATABASE ['+@DBNAME + '] SET OFFLINE WITH ROLLBACK IMMEDIATE')


            EXEC ('DROP DATABASE  ['+@DBNAME + ']') 

            set @cmdscript = 'del '+ @PhysicalPath_mdf
            exec master..xp_cmdshell @cmdscript
            set @cmdscript = 'del '+ @PhysicalPath_ldf
            exec master..xp_cmdshell @cmdscript 

            END
            
            IF NOT EXISTS ( SELECT 1 FROM SYS.DATABASES  WHERE NAME = @DBNAME )
            BREAK
    END TRY
    
    BEGIN CATCH
            SELECT ERROR_MESSAGE()  
    END CATCH
        SET @COUNTER_START = @COUNTER_START + 1
    END

SELECT @START = @START + 1
SELECT @COUNTER_START = 1
SELECT @DBNAME = ''
END

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
 [Blog]

_{Sunday, January 3, 2016 1:24 AM}

Thank you very much. Your solution is perfect!