In this section, we will see the step by step approach to attach SQL Database. 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
From the below screenshot, you can see that the SQL server does not have the AdventureWorks2017 database
From the below screenshot, 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.
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'
From the below screenshot you can see that the query is executed.
Now you can see the Adventure Works 2017 database in our SQL Server
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;
From the below screenshot you can see that the query is executed.
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