SQL Attach Database

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

SQL Attach Database Approach 1

The SQL server does not have the AdventureWorks2017 database

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

SQL Server has a sp_attach_db stored procedure to attach the database to the Server. The syntax to attach Database from MDF and LDF file is

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

Let me use the above syntax to attach Adventure Works 2017 database.

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 database in our SQL Server

SQL Attach Database Approach 2

In SQL, you can use the Create database Statement along with the Attach keyword to attach the database to the Server. And the syntax is:

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

Let me use the above syntax to attach 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 Approach 3

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 Attach.. 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. Here, we are selecting AdventurWorks2017.mdf

Click OK to attach Database in Server

Now you can see the Adventure Works2017 database.