SQL Attach Database
Detaching and attaching a SQL database, is helpful in case in case when you want to move to move a database to a different SQL Server instance. So Basically, first we detach database from SQL Server and move those files to another SQL Server and then attach files.
If you do not know how to detach database, you can refer post – Detach SQL Database.
Lets see, how to attach database in SQL Server, we will attach a Database – Adventureworks2017Â in SQL Server.
In below screenshot you can see, SQL Server does not have Adventureworks2017 database.
Attach database using SQL Server Management Studio
You can attach database using SQL Server Management Studio Object Explorer. Lets see go through step by step.
First, connect to the instance of the SQL Server Database Engine , Right Click on Databases as shown below.
Once you click on Attach, a Attach Databases dialog box opens, then click on Add button to browse the location of database that you want to attach.
After clicking on Add button, a Locate Database Files dialog box opens, there you can browse your database files location from window directory, then select MDF file of your database.
Next, click on Ok button.
After clicking on OK button, SQL Server Management Studio, displays the associated files for the database as shown below.
Here you can validate the files, If this is not correct files, then you can use the ellipsis next to each file name to select a different file as shown below.
Finally, click on OK button for attaching the database.
After clicking on Ok button. You can see object explorer, database AdventureWorks2017 is attached successfully.
Attach database using T-SQL
Lets Attach Aventureworks2017 database using T-SQL, for this we will use SQL Server System Stored Procedure sp_attach_db.
In below Screenshot, you can see the MDF and LDF files of database Adventureworks2017 in C Drive on path C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA.
In general, this is the location of Database files (mdf, ldf) that you want to attach in SQL Server and this path will be used to access the file from this location.
Lets go through step by step, first connect to the instance of the SQL Server Database Engine.
Next, Go to SSMS menu header and click on New Query, and write following T-SQL that attach database AdventureWorks2017.
Following is the T-SQL syntax, that we will use to attach database.
sp_attach_db [ @dbname= ] 'dbname' , [ @filename1= ] 'filename_n' [ ,...16 ]
[ @dbname= ] ‘dbname_’ Is the name of the database to be attached to the server. The name must be unique.
[ @filename1= ] ‘filename_n’ Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. Up to 16 file names can be specified. The parameter names start at @filename1 and increment to @filename16.
Following T-SQL attach database AdventureWorks2017, You can see query completed successfully.
EXEC sp_attach_db @dbname = N'AdventureWorks2017', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_log.ldf'
Now, Go to object explorer, and refresh the databases folder.
Now you can see AdventureWorks2017 Database there.
Also Read..
