Recently while working with database move to other location , I used tsql script to move them. There are some methods to move the database files to other like detach and attach files from new location and other one is with modifying 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 other 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 database with or without log files like mentioned as following and can be applied any of following methods to attach 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 a database like,

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 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 UI,

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