Attach and Detach Database in SQL Server

There are two main ways (via TSQL & Manually) to attach and detach databases in SQL Server -

                                                                     Attach Database

Via TSQL -

In SQL Server, you can attach a database using the "CREATE DATABASE ... FOR ATTACH" statement. This allows you to move a database from one server to another, or to reattach a detached database.

Here's an example of how to attach a database in SQL Server:

CREATE DATABASE database_name

ON (FILENAME = 'path_to_MDF_file'),

(FILENAME = 'path_to_LDF_file')

FOR ATTACH;

Manually -

To manually attach a database in SQL Server, you need to have the database files (.mdf and .ldf files) available. You can attach a database in SQL Server by using the following steps:

1. Open SQL Server Management Studio (SSMS).

2. Right-click on the "Databases" node and select "Attach...".

3. In the "Attach Databases" dialog box, click on the "Add..." button.

4. Browse to the location of the .mdf file for the database you want to attach, select the file and click on the "OK" button.

5. If the .ldf file for the database is in a different location, click on the "Add..." button again and browse to the .ldf file.

6. Click on the "OK" button to complete the attachment process. The database should now appear in the list of databases in SSMS.

Example:

Attach database AdventureWorks to the SQL Server instance using the following steps:

1. Open SQL Server Management Studio (SSMS)

2. Right-click on the "Databases" node and select "Attach...".

3. In the "Attach Databases" dialog box, click on the "Add..." button.

4. Browse to the location where you have saved the AdventureWorks database files (AdventureWorks.mdf and AdventureWorks_log.ldf)

5. Select the AdventureWorks.mdf file and click on "OK".

6. Click on the "Add..." button again and select the AdventureWorks_log.ldf file.

7. Click on the "OK" button to complete the attachment process.

The AdventureWorks database should now appear in the list of databases in SSMS.

                                                                    Detach Database

Via TSQL -

To detach a database in SQL Server, you can use the "sp_detach_db" stored procedure.

Here's an example of how to detach a database in SQL Server:

                EXEC sp_detach_db 'database_name';

Manually -

1. To detach a database in SQL Server, you can use the following steps:

2. Right-click on the database you want to detach in SSMS and select "Tasks" > "Detach...".

3. In the "Detach Database" dialog box, select the "Drop connections" option if you want to close any open connections to the database.

Click on the "OK" button to detach the database.

Example:

Detach the AdventureWorks database from the SQL Server instance using the following steps:

1. Right-click on the AdventureWorks database in SSMS and select "Tasks" > "Detach...".

2. In the "Detach Database" dialog box, select the "Drop connections" option.

3. Click on the "OK" button to detach the AdventureWorks database.

The AdventureWorks database should no longer appear in the list of databases in SSMS.

Note: You need to have sufficient privileges, such as the sysadmin role, to perform the attach and detach operations.