Text copied!
Detach database
SQL database detaching is the process of removing the database from the SQL Server instance without deleting the database files from the disk.
You may come across two different SQL database file types : "Raw Data" files and "Log" files. Raw data files are used to store the actual data of the database with ".mdf" file extension. While log files store a record of all changes made to the database with a ".ldf" file extension.
When you detach a database, it disappears from "Database folder" in object explorer and becomes unavailable to any SQL Server connection. Here's the default SQL Server database file location :
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA
To detach SQL database, you can use the graphical user interface (GUI) or Transact-SQL (T-SQL).
Here's an example of how to detach database using T-SQL :
As you can see in below image, [AdventureWorks2019] database is present. Let's detach it!
1. Run below SQL command in SSMS :
/* Option is set "true" to skip checks */
USE [master];
EXEC sp_detach_db 'AdventureWorks2019', 'true';
2. Above command will detach the [AdventureWorks2019] database from the SQL Server instance.
That's it! You have successfully detached [AdventureWorks2019] database using T-SQL.
Here's a step-by-step guide on how to detach SQL database using GUI :
1. Right-click on the database you want to detach in "Database folder" >> Select "Tasks" >> Select "Detach".
2. "Detach Database" dialog box will appear, Make sure that the "Drop Connections" option is checked >> Click "OK".
3. There you go, [AdventureWorks2019] database detached successfully.
That's it! You have successfully detached [AdventureWorks2019] database using GUI.