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