SQL SERVER – Attach a Database with T-SQL

To attach a Database with T-SQL, I use CREATE DATABASE … FOR ATTACH. A customer asked about attaching existing data and log files.

-- Reviewed attach template. Paths are on the SQL Server host.
-- USE master;
-- CREATE DATABASE [AdventureWorks] ON
-- (FILENAME = N'D:\Data\AdventureWorks.mdf'),
-- (FILENAME = N'E:\Log\AdventureWorks_log.ldf') FOR ATTACH;
-- Historical deprecated alternative, not recommended for new work:
-- EXEC sys.sp_attach_db @dbname=N'AdventureWorks',
-- @filename1=N'D:\Data\AdventureWorks.mdf',
-- @filename2=N'E:\Log\AdventureWorks_log.ldf';

My original example showed two methods. sp_attach_db is deprecated and appears only as historical syntax. Include every required file. Verify they belong to a consistent detached or offline database.

Paths refer to the SQL Server host, whose service identity needs access. Check supported database version and features. A newer engine can upgrade files so an older engine can’t reopen them. Recreate and validate server-level dependencies separately.

Preserve original files and a restore-tested backup. Incomplete, untrusted or ransomware-damaged files need an appropriate recovery investigation. Don’t delete a log file merely to force a rebuild. The linked attach and failure cases offer context.

Reference: Database attachment requirements.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – Attach or Detach Database – SQL in Sixty Seconds #068
• SQL SERVER – Unable to Attach Database – File Activation Failure – The Log Cannot be Rebuilt
• SQL SERVER – Unable to Attach Database Files – The PageAudit Property is Incorrect – Ransomware Attack
• How to Attach MDF Data File Without LDF Log File – Interview Question of the Week #078
• How to Move SQL Server MDF and LDF Files? – Interview Question of the Week #189
• SQL SERVER – Attaching and Restoring Database in Clustering Generates An Error – Notes from the Field #115
• Twitter

An attach command is not a recovery guarantee, it is a file-based operation with consistency and compatibility requirements.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.