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

View DB in a object Explorer 1

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 4

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.

SQL Attach Database 6

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.

Right Click and Choose the Attach Option 7

Clicking the option will open the following window. Next, click the Add button to add the MDF file.

Click the Add Button to Browse 8

Use this file navigation window to select the required database that you want to attach. Here, we are selecting AdventurWorks2017.mdf

Choose the DB MDF File 9

Click OK to connect DB in Server

Click Ok to Close Window 10

Now you can see Adventure Works 2017. Refer to the Detach Database article to remove it.

Object Explorer 11
Categories SQL

Comments are closed.