Jaa

Deleting a Database

  • Artikkeli

You can delete a user-defined database when it is no longer required, or if it is moved to another database or server. Deleting a database and dropping a database are synonymous. When a database is deleted, the files and their data are deleted from the disk on the server. When a database is deleted, it is permanently removed and cannot be retrieved without using a previous backup. System databases cannot be deleted.

A database can be deleted regardless of its state. These states include offline, read-only, and suspect. To display the current state of a database, use the sys.databases catalog view.

To remove a database from the current server without deleting the files from the file system, use sp_detach_db.

You should backup the master database after a database is deleted, because deleting a database updates information in master. If you have to restore master, any database that has been deleted since the last backup of master will still have references to these nonexistent databases. This may cause error messages to be raised.

Prerequisites

Before a database can be deleted, the following conditions must be met:

  • If the database is involved in log shipping, remove log shipping before dropping the database. For more information, see Log Shipping.
  • To drop a database published for transactional replication, or a database published or subscribed to for merge replication, you must first remove the replication from the database. For more information, see Removing Replication. If a database is damaged and replication cannot first be removed, frequently you still can drop the database by first using ALTER DATABASE to set the database offline and then dropping it.
  • Any database snapshot that exists on the database must first be dropped. For more information, see Database Snapshots.

To delete a database

To create a database backup

See Also

Concepts

System Databases

Other Resources

Backing Up and Restoring Databases in SQL Server

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the reference to using sp_detach_db as an alternative to dropping a database.