Skip to content

Detach and Attach SQL Server Database

    What is detaching and attaching a database

    The process of moving the data and the log files to another drive or server for performance reasons

    How to use detach and attach a database

    At times there may be a need to move a database to another physical drive or another physical server; if so, you can use the sprocs detach and attach or use a GUI

    Step by step moving a database

    The following scripts will detach and then reattach the sales database

    Find the path of the database

    sp_helpdb sales

    Set the database to a single user mode

    USE [master]

    GO

    ALTER DATABASE [Sales]

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    Detach the database using the sprocs

    USE [master]

    GO

    EXEC master.dbo.sp_detach_db @dbname = N’Sales’, @skipchecks = ‘false’

    GO

    Reattach the database using the FOR ATTACH command

    USE [master]

    GO

    CREATE DATABASE [Sales] ON

    ( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sales.mdf’ ),

    ( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sales_log.ldf’ )

     FOR ATTACH –<<use for attach to attach the sales database

    GO

    Note: when moving a database using detach and attach, you will lose the users in the original database after the completion of the move to a new server.  To resolve this, use the following sproc to link the user to the login in the new server.

    EXEC sp_change_users_login ‘Update_One’, ‘Bob’, ‘Bob’