SQL Detach Database

Let us see the steps to detach the SQL server database using the existing stored procedure and Management Studio.

SQL Detach Database Approach

From the below screenshot, you can see that the SQL server has AdventureWorks2017.

View DB in Management Studio 1

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-mentioned SQL Server sp_detach_db syntax to delete or detach AdventureWorks 2017 database.

EXEC sp_detach_db N'AdventureWorks2017', 'true';
Commands completed successfully.

Now you can see the Adventure Works 2017 removed from our Server

SQL Detach Database 3

SQL Detach Database using Management Studio

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.

Choose Sql Server Tasks and Detach Database Option from context menu 5

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.
SQL Detach Database 6

Checkmark the Drop Connections, and Click Ok

Click the Ok Button 7

The Adventure Works DW 2017 deleted from the Server

Open Object Explorer to View 8

The above process will only detach or remove the database from the server, but it will keep the MDF and LDF files within the physical location (C Drive).

View Log and Mdf file sin local hard drive 9

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.