SQL Detach Database 5

SQL Detach Database

by

Let us see the steps involved to detach the database in SQL. To demonstration this database detach, we are going to use the existing stored procedure and Management Studio.

SQL Detach Database Approach 1

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

SQL Detach Database 1

SQL Server has a sp_detach_db stored procedure to detach the database. The syntax for detach Database is

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

Let me use the above syntax to detach AdventureWorks 2017 database.

EXEC sp_detach_db N'AdventureWorks2017', 'true';

Query executed.

Messages
-------
Commands completed successfully.

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

SQL Detach Database 3

Detach Database Approach 2

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

SQL Detach Database 5

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: Checkmark this option to drop all the existing connections pointing to this database
  • Update Statistics: Checkmark this option to update existing optimization statistics.
SQL Detach Database 6

Checkmark the Drop Connections, and Click Ok

SQL Detach Database 7

The Adventure Works DW 2017 database removed from the Server

SQL Detach Database 8

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).

SQL Detach Database 9