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’