Let us see the steps to detach the database in SQL Server using the existing stored procedure and Management Studio.
SQL Detach Database Approach
From the below screenshot, you can see that the SQL server has the AdventureWorks2017.
It has a sp_detach_db stored procedure to detach the database. The syntax for this is
EXEC sp_detach_db N'Database Name', 'true';
Let me use the above syntax to delete AdventureWorks 2017.
EXEC sp_detach_db N'AdventureWorks2017', 'true';
Messages
-------
Commands completed successfully.Now you can see the Adventure Works 2017 removed from our Server
Detach Db Approach 2
You can also use Management Studio.
To do so, Right-click on the Databases folder, select the Tasks option and then select Detach.. from the context menu.
Clicking the option will open the following window to detach a database.
- DatabaseName: This will display the Name that you want to delete.
- Drop Connections: Checkmark this option to drop all the existing connections pointing to this DB.
- Update Statistics: Checkmark this option to update existing optimization statistics.
Checkmark the Drop Connections, and Click Ok
The Adventure Works DW 2017 deleted from the Server
The above process will only detach or remove the database from Server, but it will keep the MDF and LDF files within the physical location (C Drive).
