Browse: Wetory / Scripting library / Batch database drop

SQL Server - Batch database drop

by Tomáš RybnickýNo Comments

You need to drop more databases quickly. If you want to drop some database you can use SSMS GUI or T-SQL to drop single database, but if you need to perform more complex cleanup for example after successful migration you can save your time by generating batch drop T-SQL and execute it within one script.

Only thing you need is to specify databases by wildcard on line 11. Script will generate drop T-SQL into messages then you can copy and execute it on instance you want to perform cleanup on.

Warning: be aware of executing generated script as it is dropping databases physically and only way to rollback is to restore database from backup, if you are lucky you have one 🙂

DECLARE @DB SYSNAME
DECLARE @SQL VARCHAR(255)

PRINT '-- Execute folowing script to drop databases in one batch'

-- cursor for iterating through all databases
DECLARE dbname CURSOR
FOR
SELECT name
FROM sysdatabases
WHERE name LIKE 'Report%' -- specify wildcard there (%_...)
ORDER BY NAME

-- itegerate through all databases
OPEN dbname
FETCH NEXT FROM dbname INTO @DB

WHILE (@@fetch_status <> - 1)
BEGIN
	IF (@@fetch_status <> - 2)
	BEGIN
		-- database backup history cleanup
		PRINT 'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N''' + @DB + ''''
		PRINT 'GO'
		-- database dropping
		SET @SQL = 'DROP DATABASE [' + @DB + ']'
		
		-- uncomment folowing line for direct execution
		-- EXEC (@SQL)
		PRINT @SQL
		PRINT 'GO'
	END

	FETCH NEXT FROM dbname INTO @DB
END

CLOSE dbname
DEALLOCATE dbname

Results

Script messages after execution:

-- Execute folowing script to drop databases in one batch (including all databases fitting wildcard specified on line 11)
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ReportServer'
GO
DROP DATABASE [ReportServer]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'ReportServerTempDB'
GO
DROP DATABASE [ReportServerTempDB]
GO

Leave a Reply