Skip to content
Home ยป SQL Detach Database

SQL Detach Database

detachdatabase

SQL Detach Database





When you detach database from SQL Server, database is removed from the instance of SQL Server but kept the database intact within its data files and transaction log files.

These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

You can Detach database either using SQL Server Management Studio or T- SQL.

Detach database using SQL Server Management Studio

You can detach database using SQL Server Management Studio Object Explorer.ย Lets see go through step by step.

First, connect to the instance of the SQL Server Database Engine and then expand the instance.

Next, expand the Databases, select the database that you want to detach.

Here we have selected a Database AdventureWorks2017.

 

sql server database

Right Click on Database that you have selected, ย point to Tasks, and then click on Detach .

 

detachdatabase

 

After clicking on Detach.., a Detach Database dialog box appears, here you can see following options.

  • Database Name: This displays the Name of the database that you want to detach.
  • Drop Connections: Check this option to drop all the existing connections pointing to this database.
  • Update Statistics: Check this option to update existing optimization statistics. ย If this option is unchecked, statistics will remain as they were at the time you detached the database.

Lets check the Drop connections, because you can not detach the database with active connection so you need to disconnect any active connections by selectingย Drop Connections.

After that click on Ok button.

 

 

Once you click on OK button, you see can AdventureWorks2017 database removed from SQL Server as shown in below screenshot.

 

 

Detach database using T – SQL

Lets detach database using T-SQL, for this we will use SQL Server System Stored Procedure sp_detach_db.

Lets go through step by step, first connect to the instance of the SQL Server Database Engine.

Next, Go to SSMS menu header and click on New Query, and write following T-SQL that detach database AdventureWorks2017 from SQL Server.

 

EXEC sp_detach_db N'AdventureWorks2017', 'true';

 

detach database sp_detach_db

 

As can see, command completed successfully, now Go to Databases folder in SSMS, right click on it then click on Refresh.

 

 

After refreshing, you will see AdentureWorks2017 database removed from SQL Server.

 

 

Also Read..

Attach SQL Database

Create SQL Database

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 2,591 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.