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

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.

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

Click OK to connect DB in Server

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

Comments are closed.