Using DROP DATABASE to Erase an MS-SQL Server Database and Transaction Log

Dropping (deleting) databases you no longer need frees up disk space. The primary rule to follow: Be careful! You cannot easily undo an executed DROP DATABASE statement. As such, always back up the database before dropping (erasing) it. Having a full backup will save you a lot of headaches if the user decides he or she needs "one more thing" from the database-right after you erase it, of course.

Only the system administrator (sa) or a user with dbcreator or sysadmin privilege can drop a database. You cannot drop the MASTER, MODEL, or TempDB database.

The syntax of the DROP DATABASE statement is:

DROP DATABASE 
             [,,  ]

Thus, to remove the MARKETING database you created in Tip 42, "Using the MS-SQL Server Enterprise Manager to Create a Database and Transaction Log," perform the following steps:

1. Start the MS-SQL Server Query Analyzer (as you learned to do in Tip 38, "Using the MS-SQL Server Query Analyzer to Execute SQL Statements"), or start the Enterprise Manager (as you learned to do in Tip 42) and select the Tools menu, SQL Server Query Analyzer option.

2. Enter the DROP DATABASE statement in the Query Analyzer's Query pane. For the current project, type

   DROP DATABASE marketing.
   

3. Press Ctrl+E (or select the Query menu, Execute option).

   After you complete Step 3, the Query Analyzer will attempt to delete the database and log file. If Query Analyzer successfully deletes the MARKETING database and transaction log, it will display the following in the Results pane of the Query Analyzer application window:

    Deleting database file 'C:\MSSQL7\data\MARKETING_Data.MDF'.
   Deleting database file 'C:\MSSQL7\data\MARKETING_Log.LDF'.