SQL Detach Database

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.

 

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

 

 

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';

 

 

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 Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,591 total views,  2 views today