How to Attach and Detach a Database in SQL Server

Quick answer: Detaching a database takes it offline and releases its .mdf and .ldf files so you can copy or move them. Attaching brings those files back online as a database. It is a quick way to move a database between servers when both use compatible SQL Server versions. Do it in SSMS or with T-SQL.

Detach a database

In SSMS, right-click the database > Tasks > Detach. Or with T-SQL:

USE master;
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'YourDatabase';

Once detached, copy the .mdf and .ldf files to the new location.

Attach a database

CREATE DATABASE YourDatabase
ON (FILENAME = 'D:\Data\YourDatabase.mdf'),
   (FILENAME = 'D:\Data\YourDatabase_log.ldf')
FOR ATTACH;

Or in SSMS: right-click Databases > Attach, then add the .mdf.

Attach vs backup/restore

Attach/detach moves the live files and needs the database offline briefly. A backup and restore (.bak) leaves the source online and is safer for a production migration. Use backup/restore when the source must stay available.

Frequently asked questions

Can I attach a database from a newer SQL Server?
No - you cannot attach files from a newer version onto an older engine. The target must be the same or newer.

What if the .ldf log file is missing?
SQL Server can often rebuild a missing log during attach, but always keep both files together when possible.

On a SoftSys managed Windows VPS our team handles database moves for you as part of managed SQL Server.