DROP DATABASE (Transact-SQL)DROP DATABASE (Transact-SQL)

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是平行處理資料倉儲Parallel Data Warehouseyes平行處理資料倉儲Parallel Data Warehouse

可從一個 SQL ServerSQL Server 執行個體,移除一個或多個使用者資料庫或資料庫快照集。Removes one or more user databases or database snapshots from an instance of SQL ServerSQL Server.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- SQL Server Syntax
DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]
-- Azure SQL Database, Azure Synapse Analytics and Analytics Platform System Syntax
DROP DATABASE database_name [;]

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法,請參閱舊版文件To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

引數Arguments

IF EXISTS 適用於SQL ServerSQL Server(從 SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前的版本)。IF EXISTS Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

只有在資料庫已存在時,才能有條件地將其卸除。Conditionally drops the database only if it already exists.

database_name 指定要移除的資料庫名稱。database_name Specifies the name of the database to be removed. 若要顯示資料庫清單,請使用 sys.databases 目錄檢視。To display a list of databases, use the sys.databases catalog view.

database_snapshot_name 適用於SQL Server 2008SQL Server 2008 及更新版本。database_snapshot_name Applies to: SQL Server 2008SQL Server 2008 and later.

這是要移除的資料庫快照集名稱。Specifies the name of a database snapshot to be removed.

一般備註General Remarks

無論資料庫的狀態為離線、唯讀或受到質疑等,都可以卸除該資料庫。A database can be dropped regardless of its state: offline, read-only, suspect, and so on. 若要顯示資料庫的目前狀態,請使用 sys.databases 目錄檢視。To display the current state of a database, use the sys.databases catalog view.

若要重新建立已經卸除的資料庫,只有還原備份一途。A dropped database can be re-created only by restoring a backup. 資料庫快照集無法備份,因此也無法還原。Database snapshots cannot be backed up and, therefore, cannot be restored.

在卸除資料庫時,應該備份 master 資料庫When a database is dropped, the master database should be backed up.

卸除資料庫時,不但會從 SQL ServerSQL Server 執行個體中刪除該資料庫,同時也會刪除該資料庫所用的實體磁碟檔。Dropping a database deletes the database from an instance of SQL ServerSQL Server and deletes the physical disk files used by the database. 如果資料庫或其任何一個檔案在卸除時離線,磁碟檔就不會被刪除。If the database or any one of its files is offline when it is dropped, the disk files are not deleted. 這些檔案可以利用 [Windows 檔案總管],以手動方式刪除。These files can be deleted manually by using Windows Explorer. 若要從目前伺服器中移除資料庫,而不刪除檔案系統中的檔案,請使用 sp_detach_dbTo remove a database from the current server without deleting the files from the file system, use sp_detach_db.

警告

卸除具有與其建立關聯之 FILE_SNAPSHOT 備份的資料庫會成功,但不會刪除具有相關聯快照集的資料庫檔案,以避免參考這些資料庫檔案的備份不正確。Dropping a database that has FILE_SNAPSHOT backups associated with it will succeed, but the database files that have associated snapshots will not be deleted to avoid invalidating the backups referring to these database files. 檔案將會被截斷,但實體不會被刪除,以保存完整的 FILE_SNAPSHOT 備份。The file will be truncated, but will not be physically deleted in order to keep the FILE_SNAPSHOT backups intact. 如需詳細資訊,請參閱 SQL Server 備份及還原與 Microsoft Azure Blob 儲存體服務For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. 適用於SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前的版本Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version.

SQL ServerSQL Server

卸除資料庫快照集時,會從 SQL ServerSQL Server 執行個體刪除資料庫快照集,並刪除快照集所使用的實體 NTFS 檔案系統疏鬆檔案。Dropping a database snapshot deletes the database snapshot from an instance of SQL ServerSQL Server and deletes the physical NTFS File System sparse files used by the snapshot. 如需資料庫快照集使用疏鬆檔案的資訊,請參閱資料庫快照集For information about using sparse files by database snapshots, see Database Snapshots. 卸除資料庫快照集會清除 SQL ServerSQL Server 執行個體的計畫快取。Dropping a database snapshot clears the plan cache for the instance of SQL ServerSQL Server. 清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 針對每次清除計畫快取的快取存放區,SQL ServerSQL Server 錯誤記錄檔會包含下列資訊訊息:「由於某些資料庫維護或重新設定作業,SQL ServerSQL Server 的 '%s' 快取存放區 (計畫快取的一部分) 發生 %d 次快取存放區排清」。For each cleared cachestore in the plan cache, the SQL ServerSQL Server error log contains the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

互通性Interoperability

SQL ServerSQL Server

若要卸除針對異動複寫而發行的資料庫,或是針對合併式複寫而發行或訂閱的資料庫,必須先從該資料庫移除複寫。To drop a database published for transactional replication, or published or subscribed to merge replication, you must first remove replication from the database. 如果資料庫損毀,或者無法先移除複寫,或者兩種情況都有,多半還是能夠卸除資料庫,方法是利用 ALTER DATABASE,將資料庫設為離線,然後再卸除它。If a database is damaged or replication cannot first be removed or both, in most cases you still can drop the database by using ALTER DATABASE to set the database offline and then dropping it.

如果資料庫有涉及記錄傳送,請在卸除資料庫之前移除記錄傳送。If the database is involved in log shipping, remove log shipping before dropping the database. 如需詳細資訊,請參閱 關於記錄傳送For more information, see About Log Shipping.

限制事項Limitations and Restrictions

無法卸除系統資料庫System databases cannot be dropped.

DROP DATABASE 陳述式必須執行自動認可模式,且不能在明確或隱含的交易中。The DROP DATABASE statement must run in autocommit mode and is not allowed in an explicit or implicit transaction. 自動認可模式是預設的交易管理模式。Autocommit mode is the default transaction management mode.

您無法卸除目前正在使用的資料庫。You cannot drop a database currently being used. 也就是開放給任何使用者讀取或寫入的資料庫。This means open for reading or writing by any user. 從資料庫移除使用者的一種方法是使用 ALTER DATABASE 將資料庫設為 SINGLE_USER。One way to remove users from the database is to use ALTER DATABASE to set the database to SINGLE_USER.

警告

這不是防呆方法,因為任何執行緒所進行的第一個連續連線都會收到 SINGLE_USER 執行緒,導致您的連線失敗。This is not a fail-proof approach, since first consecutive connection made by any thread will receive the SINGLE_USER thread, causing your connection to fail. SQL Server 未提供卸除資料庫以低於負載的內建方法。Sql server does not provide a built-in way to drop databases under load.

SQL ServerSQL Server

在卸除資料庫之前,必須先卸除該資料庫上的任何資料庫快照集。Any database snapshots on a database must be dropped before the database can be dropped.

卸除針對 Stretch Database 所啟用的資料庫,並不會移除遠端資料。Dropping a database enable for Stretch Database does not remove the remote data. 如果您想要刪除遠端資料,則必須手動將它移除。If you want to delete the remote data, you have to remove it manually.

Azure SQL DatabaseAzure SQL Database

您必須連接至 master 資料庫,才能卸除資料庫。You must be connected to the master database to drop a database.

DROP DATABASE 陳述式必須是 SQL 批次中的唯一陳述式,而且您一次只能卸除一個資料庫。The DROP DATABASE statement must be the only statement in a SQL batch and you can drop only one database at a time.

Azure Synapse Analytics (SQL 資料倉儲)Azure Synapse Analytics (SQL Data Warehouse)

您必須連接至 master 資料庫,才能卸除資料庫。You must be connected to the master database to drop a database.

DROP DATABASE 陳述式必須是 SQL 批次中的唯一陳述式,而且您一次只能卸除一個資料庫。The DROP DATABASE statement must be the only statement in a SQL batch and you can drop only one database at a time.

權限Permissions

SQL ServerSQL Server

需要資料庫的 CONTROL 權限,或是 ALTER ANY DATABASE 權限,或 db_owner 固定資料庫角色的成員資格。Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role.

Azure SQL DatabaseAzure SQL Database

只有伺服器層級主體登入 (由佈建程序所建立) 或 dbmanager 資料庫角色成員才能卸除資料庫。Only the server-level principal login (created by the provisioning process) or members of the dbmanager database role can drop a database.

平行處理資料倉儲Parallel Data Warehouse

需要資料庫的 CONTROL 權限,或是 ALTER ANY DATABASE 權限,或 db_owner 固定資料庫角色的成員資格。Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role.

範例Examples

A.A. 卸除單一資料庫Dropping a single database

下列範例會移除 Sales 資料庫。The following example removes the Sales database.

DROP DATABASE Sales;

B.B. 卸除多個資料庫Dropping multiple databases

適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

下列範例會移除每一個列出的資料庫。The following example removes each of the listed databases.

DROP DATABASE Sales, NewSales;

C.C. 卸除資料庫快照集Dropping a database snapshot

適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

下列範例會移除名為 sales_snapshot0600 的資料庫快照集,而不會影響來源資料庫。The following example removes a database snapshot, named sales_snapshot0600, without affecting the source database.

DROP DATABASE sales_snapshot0600;

另請參閱See Also