To detach a database,
Right click on the database > Tasks > Detach > OK. (There are two options during the detach action; dropping all connections and updating statistics. Select the drop connection will drop all the active connection. Please note that by default, detaching the database retain out-to-date optimization statistics. Also, detaching database require no active connection to the database.)
EXEC sp_detach_db 'YourDatabase'
(By default, updating statistic is set to NULL. Include syntax ', True' parameter if updating statistics is desired.)
To attach a database,
Right click Databases > Attach > Add > Select the path of the database MDF file > OK.
USE master; CREATE DATABASE YourDatabase ON (FILENAME = 'the physical path of your database file') LOG ON (FILENAME = 'the physical path of your log file') FOR ATTACH;
Log file is optional if no log file is available. The ATTACH action will create a log file automatically if no log file is assigned.
Please noted that when you detach a database files, move to other location, and attach to another instance, you have to recreate all the metadata of the dependent entities and objects stored in master and msdb databases such as server level trigger, logins, jobs on the destination SQL Server instance.
There is also another option OFFLINE and ONLINE. Here is the blog about if you should use OFFLINE or DETACH.