Attach SQL MDF File Through SSMS & T-SQL

In this decade of advance technology the availability and usability of optional resources increases day by day and for this Microsoft launched its SQL Server enhanced versions which are easy to handle and maintains SQL database with strong desirability and reliability to provide more comfort to their users. Users created database to maintain information and specific data which was very crucial for him and they need to store their database to the SQL Server in term of security and safety point of view. But the main trouble arises when they do some specific task like migrating or attaching MDF database files to the SQL Server. But Before discussing on how to attach SQL databases to the SQL Server you need to locate the MDF file and Log file to specific database folder. MDF file is root of the SQL database it acts a primary storage where as LDF file is known as the log file which maintains the transactional operations on the SQL database. Both these files are the crucial and necessary files for correct of functioning database.

Two Ways To Attach SQL Server MDF File

  • Through SQL Server Management Studio
  • By running Transact-SQL Script

Attach SQL Database Via SQL Server Management Studio

If you want to attach SQL database MDF file through SQL Server Management studio then you need to connect to an instance of the SQL DB Engines and explore it.

  • Firstly open SQL Server Management Studio

Go to Start button -> All Programs –> MS SQL Server -> SQL Server Management Studio

ssms start

  • Please wait for few moments so that it opens and then Login in it by entering its credential details

ssms login window

  • Then suddenly next window showing the object explorer items on left side (local)(SQL Server 10.50.1600 – WIN 73/Admin) click on it. After clicking on it its sub elemets list opens.

ssms objects

  • Select and Right click on Databases then choose Attach option

ssms attach data file

  • On clicking Attach option the window appears on the screen call Attach Database Window then click on Add button

ssms database attachment window

  • It displays a list of SQL Server database Files select it and click on Ok Button

sql database files list

  • Now Confirm the MDF file and LDF file location and go to Ok Button

confirm MDF file

  • Now successfully you have attached Database Name Test to the SQL Server through SQL Server Management Studio

sql database attached

Attach SQL MDF File Via Transact-SQL

  • Connect to the Database Engine and click New Query.

CREATE DATABASE MyAdventureWorks
ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
(FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
FOR ATTACH;

sql recovery banner