SQL Server : Detach/Attach Database

You might want to detach a database because you want to move it to a new server.

Detaching a database causes you to lose all the metadata about that database. Detach an existing database using the following command.

USE master
GO

sp_detach_db 'database-name', 'true';
GO

Attach a detached database. This could be a database you just detached on the same server, or one you’ve recently copied from another server.

USE master

GO


CREATE DATABASE database-name
ON (FILENAME = 'C:\path\to\file.mdf')
FOR ATTACH;

Check the databases present using the following query.

SELECT name FROM sys.databases;

To detach a database using the GUI do the following.

• Right-click on database.
• Select “Tasks > Detach”

For more information see:

• sp_detach_db (Transact-SQL)