Drop all databases from server

A stitch in time saves nine

Before considering database mass deletion, approach it as if you're defusing a ticking bomb. Plan out the following steps:

• Be Ready for Worst: Back up all databases–no one wants a Titanic data disaster.
• Are they Expendable: Confirm no essential databases are targeted.
• Alert Users: Inform everyone–a polite reminder never hurts.
• Right Time: Schedule deletion during low traffic hours.

Treasures among the junk

Be certain to spare the system databases: master, model, msdb, and tempdb. These are your SQL Server's life support and should be excluded from any delete scripts. They are the cute hamsters running on wheels, powering up your SQL Server.

GUI way - SQL Server Management Studio Method

For those UI lovers, SQL Server Management Studio (SSMS) has got your back when it comes to dropping databases:

1. Link to the required instance of the Database Engine.
2. Unleash the Object Explorer, open the canopy of Databases node.
3. Multiselect the disposable databases using SHIFT or CTRL.
4. Right-click and choose Delete–not painful at all!
5. In the displayed dialog box, opt for Close Existing Connections and Continue After Error– like a champ!
6. Press OK and boom! The selected databases are wiped out.

T-SQL Spice (Safety Checks)

The T-SQL script paired with a few safety measures can be a robust method to drop databases:

1. Stand guard using DB_ID(). It's like asking, "Friend or foe?"
2. Kill any lingering connections. No more "It's alive!" moments.
3. Validate the compatibility with older versions–remember, grandparents need care too! (SQL Server 2005 in our case).

/* Suit Up! */
DECLARE @DbName NVARCHAR(255)
DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases 
WHERE database_id > 4 AND state_desc = 'ONLINE' /* Online and ready to go… down! */

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DbName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    EXEC ('ALTER DATABASE ' + @DbName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE /* Lone Ranger mode */')
    EXEC ('DROP DATABASE ' + @DbName /* Drop it like it's hot! */)
    FETCH NEXT FROM db_cursor INTO @DbName  
END 

CLOSE db_cursor  /* Curtain call */
DEALLOCATE db_cursor  /* Lights out */


Command-line alternatives

sqlcmd way

Execute an entire symphony of deletion commands in one line with just sqlcmd.exe:

/* Command-line can be fun too */
sqlcmd -S YourServerName -E -Q "EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN DROP DATABASE [?] END'"


Powershell Strategy

If PowerShell feels more homely, or you're dealing with SQL Server 2005, here's your script to the rescue:

/* Let there be power(shell) */
$server = 'YourServerInstanceName'
$databases = Invoke-Sqlcmd -ServerInstance $server -Query "SELECT name FROM sys.databases WHERE database_id > 4"
foreach ($db in $databases) {
    Invoke-Sqlcmd -ServerInstance $server -Query "DROP DATABASE $($db.name) /* Another one bites the dust */"
}


Checklist for Professionals

1. Perform double-checks on target databases before the mission.
2. Counter-check post-cleanup: Trust, but verify.
3. Update script paths ($sqlCmdPath) for successful PowerShell runs.