SQL Server – How to Detach a Database
To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.
There are two different methods available to detach a database from a server.
Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:
To detach a database using SQL Server Management Studio:
1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.
2. Check the "Drop Connections" checkbox and click "OK" to detach the database
Now the database is detached from server and you can move .mdf and .ldf files related to database.
Method 2: Detach database using T-SQL:
To detach a database from a server using T-SQL you can use below code:
ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'
Command(s) completed successfully.
This detaches database from server.
Hope This Helps!