SQL Detach Database

In this article we will show you, the steps involved to detach SQL Database. For this demonstration, we are going to use the existing stored procedure, and SQL Server management Studio.

SQL Detach Database Approach 1

From the below screenshot, you can see that the SQL server have the AdventureWorks2017 database

SQL Server has sp_detach_db stored procedure to detach database from the Server.

Syntax to detach Database from Server is

EXEC sp_detach_db N'Database Name', 'true';

Let me use the above syntax to detach Adventure Works 2017 database.

EXEC sp_detach_db N'AdventureWorks2017', 'true';

From the below screenshot you can see that the query is executed.

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

SQL Detach Database Approach 2

You can also use SQL Management Studio to detach any database. To do so, Right-click on the Databases folder, and select the Tasks option and then select Detach.. from the context menu.

Clicking the Detach.. option will open the following window.

• Database Name: This will display the Name of the database that you want to detach.
• Drop Connections: Check mark this option to drop all the existing connections pointing to this database
• Update Statistics: Check mark this option to update existing optimization statistics.

Check mark the Drop Connections, and Click Ok

Now you can see that the Adventure Works DW 2017 database is removed from the Server

The above process will only detach the database from Server, but it will keep the Mdf, and ldf files within the physical location (C Drive)