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.

SQL Attach Database Approach

The server does not have the AdventureWorks2017

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

It has a sp_attach_db stored procedure to add the database to the Server. The syntax to load DB from MDF and LDF files is

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 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 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 using Management Studio Approach

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.

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

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

Click OK to connect DB in Server

Now you can see the Adventure Works2017.