How to Attach and Detach Databases - SQL Server DBA Tutorial

Attach and Detach Databases

To attach and detach databases in Microsoft SQL Server (MSSQL), you can use Transact-SQL (T-SQL) statements or SQL Server Management Studio (SSMS). Here's how you can perform these operations:

Detach a Database:

Using T-SQL:

1.     Open SQL Server Management Studio or connect to your MSSQL instance using a T-SQL client.

2.     Execute the following T-SQL statement to detach a database:

SQL Copy code

                        USE [master]

                        GO

                        ALTER DATABASE [Test] SET  SINGLE_USER WITH ROLLBACK                             IMMEDIATE

                        GO

                        USE [master]

                        GO

                        EXEC MASTER.dbo.sp_detach_db @dbname = N'Test'

                        GO

·      Replace "YourDatabaseName" with the name of the database you want to detach.

Using SQL Server Management Studio (SSMS):

1.     Open SQL Server Management Studio and connect to your MSSQL instance.

2.     In the Object Explorer pane, expand the "Databases" folder and locate the database you want to detach.

3.     Right-click on the database, go to "Tasks," and select "Detach..."

4.     In the "Detach Database" dialog box, review the list of databases selected for detachment.

5.     Optionally, you can choose to drop existing connections by selecting the checkbox "Drop Connections."

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

Attach a Database:

Using T-SQL:

1.     Open SQL Server Management Studio or connect to your MSSQL instance using a T-SQL client.

2.     Execute the following T-SQL statement to attach a database:

            SQL Copy code

                        USE [master]

                                       GO

                                       CREATE DATABASE [TestDB] ON ( FILENAME = N'C:\Program Files\Microsoft                                                                                        SQLServer\MSSQL15.SQLEXPRESS\MSSQL\DATA\Test.mdf' ),

                                        ( FILENAME = N'C:\Program Files\Microsoft SQL                                                                                                                                            Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Test_log.ldf' )
                                        FOR ATTACH
                                        GO

·      Replace "YourDatabaseName" with the desired name for your database. Update the file paths ('C:\Path\To\YourDatabase.mdf' and 'C:\Path\To\YourDatabase_log.ldf') to match the actual locations of your database files.

Using SQL Server Management Studio (SSMS):

1.     Launch SQL Server Management Studio and connect to your MSSQL instance.

2.     In the Object Explorer pane, right-click on the "Databases" folder and select "Attach..."

3.     In the "Attach Databases" dialog box, click the "Add..." button to browse and select the database files (.mdf, .ndf, .ldf) you want to attach.

4.     Optionally, you can specify a new database name under the "Attach As" column to attach the database with a different name.

5.     Review the list of selected files and ensure they are correct.

6.     Click the "OK" button to attach the database.

Please note that when attaching or detaching databases, ensure that you have the necessary permissions and consider any dependencies or requirements of the database, such as compatibility levels, collation settings, or security configurations. Also, take appropriate backups before performing any detach operations to prevent data loss or corruption.

 Copy Below Script and Practise at your own Machine.

--Detach Script

USE [master]

GO
ALTER DATABASE [Test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE [master]

GO
EXEC MASTER.dbo.sp_detach_db @dbname = N'Test'
GO

-Attach Script

USE [master]

GO

CREATE DATABASE [TestDB] ON ( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Test.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\Test_log.ldf' )
FOR ATTACH
GO