Text copied!
Attach database
SQL database attaching is the process of connecting an existing "database file" to SQL server database instance.
You may come across two different SQL database file types : "Raw Data" files and "Log" files. Raw data files are used to store the actual data of the database with ".mdf" file extension. While log files store a record of all changes made to the database with a ".ldf" file extension.
Here's the default SQL Server database file location. You can attach the [AdventureWorks2019] database to SQL Server using this file path :
C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA
To attach SQL database, you can use the graphical user interface (GUI) or Transact-SQL (T-SQL).
Here's an example of how to attach database using T-SQL :
As you can see in below image, [AdventureWorks2019] database is not present. Let's attach it!
1. Run below SQL command in SSMS :
USE [master];
CREATE DATABASE [AdventureWorks2019] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AdventureWorks2019_log.ldf' )
FOR ATTACH;
2. Above command will attach the [AdventureWorks2019] database to the SQL Server instance.
That's it! You have successfully attached [AdventureWorks2019] database using T-SQL.
Here's a step-by-step guide on how to attach SQL database using GUI :
1. Right-click on the "Databases folder" in the Object Explorer >> Select "Attach".
2. "Attach Databases" dialog box will appear, click the "Add" button.
3. Browse to the database files location (.mdf and .ldf files) >> Select the .mdf file >> Click on the "OK" button.
4. Both .mdf and .ldf files will be selected automatically >> Click on the "OK" button to attach the database.
5. There you go, [AdventureWorks2019] database attached successfully.
That's it! You have successfully attached [AdventureWorks2019] database using GUI.