Detach and Attach database in SQL Server by serverku

Recently, while working with the database move to another location, I used the tsql script to move them. There are some methods to move the database files to another like detach and attach files from the new location and the other one is worth modifying the database file location. So, lets see both of them as how can we move database files.

Option #1 : Using Modify file
Here we can use MODIFY clause to move database file after copying required place like as following,

USE master 
Go 
ALTER DATABASE TestDB SET offline 

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB _Data, FILENAME = 'D:\TestDB _Data.mdf') 

ALTER DATABASE TestDB 
MODIFY FILE (NAME = TestDB _Log, FILENAME = 'D:\TestDB _Log.ldf') 

ALTER DATABASE TestDB SET online 

This is one of the fist method to move databases to another location.

Option #2 : Using Detach and Attach
We can use detach and attach method to move database files also. Let’s see that also

ALTER DATABASE TestDB 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TestDB'
GO

This will detach database with drop existing connection. After detaching database, we can attach the database with or without log files like to mention as follows and can be applied any of following methods to attach the database again.
#1. Attaching database with both data and log files which will use existing mdf and ldf files,

EXEC sp_attach_db @dbname = N'TestDB', 
    @filename1 = N'D:\TestDB_Data.mdf',  
    @filename2 = N'D:\TestDB_Log.ldf';

#2. We can also generate or rebuild new log file with existing mdf file and attach like a database,

EXEC sp_attach_db @dbname = N'TestDB', 
 @filename1 = N'D:\TestDB_Data.mdf' 
 FOR ATTACH_REBUILD_LOG; 

#3. Creating a new database with same or different name using the same existing mdf file and rebuilding ldf file,

CREATE DATABASE TestDB ON
 (FILENAME =  N'D:\TestDB_Data.mdf') 
 FOR ATTACH_REBUILD_LOG

#4.  Creating a new database with same or different name using same existing mdf and ldf files,

CREATE DATABASE TestDB ON
 (FILENAME = 'D:\TestDB_Data.mdf')  ,
 (FILENAME = 'D:\TestDB_Log.ldf')
 FOR ATTACH;

These all are about to move tsql script, We can also perform the same thing with the UI,

These are various methods to move databases to another location. Hope you liked this post and also usable for you.