Detach and Attach Database.

Problem: How to detach and attach a database, where will this be useful process?

Solution: Database Detach and Attach (SQL Server)

The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.

A Database cannot be detached from the SQL Server Instance

·         if the database currently replicated.

·         if the database is in the mirroring.

·         if a snapshot of database is created and still exists.

·         database cannot be a system database.

A database cannot be attached to a SQL Server Instance

·         If there is a database file restore happening

This detaching and attaching of data files and log files will be helpful in a process of upgrades or patching the SQL Server and reducing the down time for application.

Example: In this example, I would like to show we can detach a database and get the database back online with attaching it in a quick second.

--Detaching a test Database

exec sp_detach_db @dbname='Management' 

    , @keepfulltextindexfile='true'; 

--This will be offline once the database detached.

Select state_desc from sys.master_files where name='Management'

--Attaching back again.

CREATE DATABASE AttachedDB  

    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\Management.mdf'),  

    (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA\Management_Log.ldf')  

    FOR ATTACH; 

--This will be ONLINE once the database is attached back again to instance.          

Select state_desc from sys.master_files where name='Management'

OUTPUT:

AS we can see the Database properties from the options the files are still the same data and log files under a database name we choose to attach.