Dropping Databases

Dropping a database removes all its objects and frees the storage space allocated to it. All references to the dropped database in the master database system tables are also removed. After you drop a database, all the login IDs that used that database as their default database will use master as their default database instead.

You cannot drop the master, model, or tempdb databases. You cannot drop a database that is open for reading or writing by any user. You cannot drop a published database (a database that is participating in replication by publishing some or all of its tables). However, you can drop databases marked suspect or offline. For information on suspect and unrecovered databases, see Part 8, Troubleshooting.

Only the database owner and the SA have permission to drop databases. This permission cannot be transferred.

Databases can be dropped by using the Manage Databases and Server Manager windows.

    To drop a database from the Manage Databases window

1. From the Server Manager window, select a server, and then from the toolbar, choose the Manage Databases button.

   

   The Manage Databases window appears.

   

2. Select a database name, and then choose the Delete Database button.

   

   A confirmation dialog box appears.

3. Choose Yes.

   The database is dropped.

Another way to drop a database is to delete it from the Server Manager window.

    To delete a database using the Server Manager window

1. From the Server Manager window, select a server, and then open the Databases folder for that server.

   

2. Click the right mouse button on the database, and then from the drop-down menu that appears, choose Delete. Or select the database and press the Del key.

   A confirmation dialog box appears.

3. Choose Yes.

   The database is dropped.

Note After you drop a database, you should back up the master database.

You can also use the DROP DATABASE statement to drop a database. For information, see the Microsoft SQL Server Transact-SQL Reference.

Dropping a database removes references to that database from the sysdatabases and the sysusages tables.