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’