SQL Server Database DETACH and ATTACH

Sometimes databases need to be moved to another instance or server. In this scenario, administrator could use detach and attach options. When you DETACH a database, it is removed from the instance of SQL Server, but keeping the database and log files intact. (There are certain limitations and restrictions preventing a database from being detached, e.g. participation in replication, mirroring, snapshot, or is a system database). The detached database no longer exist in the particular SQL Server instance. request to database returns error. To ATTACH a database to an instance is basically creating a database on the instance with existing database files (with/without log file).

To detach a database,

Using SSMS,
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.)

Using T-SQL

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,

Using SSMS,
Right click Databases > Attach > Add > Select the path of the database MDF file > OK.

Using T-SQL,

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.