SQL Attach Database

In this section, we will see the step by step approach to attach Database in SQL Server. For this demonstration, we are going to use the existing stored procedure, Create Statement, and Management Studio.

The server does not have the AdventureWorks2017

You can see that the AdventureWorks MDF and LDF files are there in C Drive

The list of MDF and LDF files in a Hard drive 2

SQL Attach Database using the Query

It has a sp_attach_db stored procedure to add the database to the Server. The SQL syntax to attach a Database from MDF and LDF files is shown below.

EXEC sp_attach_db @dbname = N'Database Name',
	@filename1 = 'location\MDFFileName.mdf',
	@filename1 = 'location\LDFFileName.ldf'

Let me use the above syntax to connect Adventure Works 2017.

EXEC sp_attach_db @dbname = N'AdventureWorks2017',
	@filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017.mdf',
	@filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_log.ldf'

Messages
-------
Commands completed successfully.

Now you can see the Adventure Works 2017 in our SQL Server

SQL Attach Database Approach 2

You can use the Create database Statement along with the Attach keyword in the SQL Server to perform this. And the syntax is as shown below:

CREATE DATABASE AdventureWorks2017
	ON (FILENAME = 'location\MDFFileName.mdf'),
	   (FILENAME =  'location\LDFFileName.ldf')
FOR ATTACH;

Let me use the above syntax to add a database called AdventureWorks.

CREATE DATABASE AdventureWorks2017
     ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017.mdf'),
	(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_log.ldf')
FOR ATTACH;

See that the query is executed.

Messages
-------
Commands completed successfully.

Now you can see the Ad 2017.

Attach Database using Management Studio

You can also use Management Studio to attach any database. To do so, Right-click on the Databases folder and select the Attach.. option from the context menu.