SQL SERVER – Attach a Database with T-SQL

Yesterday one of my clients of Comprehensive Database Performance Health Check reached out to me asking for help in attaching the database files to the server. Let us see today we can attach a database with T-SQL.

There are two different methods to attach the database. Let us see both of them.

Method 1: Create a Database with T-SQL

This is my preferred method and I use it all the time.

USE [master]
GO
CREATE DATABASE [AdventureWorks] ON 
( FILENAME = N'D:\Data\AdventureWorks.mdf' ),
( FILENAME = N'E:\Log\AdventureWorks_log.ldf' )
 FOR ATTACH
GO

Method 2: sp_attach_db

This is used to be my preferred method before I started to use Method 1.

USE [master]
GO
EXEC sp_attach_db @dbname = N'AdventureWorks',   
    @filename1 =   'D:\Data\AdventureWorks.mdf',   
    @filename2 =   'E:\Log\AdventureWorks_log.ldf'
GO

Well, that’s it. It is pretty simple to attach databases with the T-SQL script. Here is a few associated blog post on the same topic, which you may find useful.

• 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

If you have any such questions, you can reach me via Twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)