How to MOVE database MDF and LDF file to New Drive location path step by step in SQL Server

Step 1

take data file path use sp_help db and save it to notepad and take screen shot and paste it to paint

Ex:

method 1

sp_helpdb 'databasename'

method 2

SELECT name, physical_name AS Current_Location
   FROM sys.master_files
          WHERE database_id = DB_ID(N'Testdbgrowth');

          GO

Step 2

Change file path through ALTER DATABASE MODIFY FILE query

USE master;

GO

ALTER DATABASE db_name

MODIFY FILE (NAME = dbMDFlogicalname, FILENAME = 'E:\new_location\Datafile name.mdf');

GO

ALTER DATABASE db_name

MODIFY FILE (NAME = dbLDFlogicalname, FILENAME = 'E:\new_location\Logfilename.ldf');

GO

Step 3:

verify any users or active connections still using the database or not using s p_who 2 query if any connection exists change single user and multi user mode immediately  to close connections as see 

below

USE master;
GO

-- Put database into single user mode closing open connections
ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

-- Put it back into multi user mode
ALTER DATABASE db_name

SET MULTI_USER;

Step 4:

take offline for DB

Step 5:

copy physical file from source location to new location (don't rename if you should rename need to give same in alter query)

Step 6:

bring database online

Step 7:

verify s p_ h e l p d b command to new path available on M D F and L D F 

Demo:

Step 1:

we can see using 

s p _help db 'database name' to take the data file path 

or we can use  s y s.master_files to get database files info

or we can s p_help file to get this m d f and l d f info (note it will be work when we point db see below)

you can see the M D F available location see below

you can see the L D F available location see below

Kindly take it to notepad for future reference  or take it screenshot and save it on paint u r wish

Step 2

now we need to prepare query to take effect of new path location as see below

USE master;
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth, FILENAME = 'D:\SQL\Data file\Testdbgrowth.mdf');
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth_log, FILENAME = 'D:\SQL\Log\Testdbgrowth_log.ldf');

GO

now we need to execute to take effect

it clearly says it will effect when next db went online/offline

Step 3:

now we need check whether any db is any connection using or not to take db offline without any issue

Step 4

now we are going to take db offline

now it shows db offline status

Step 5:

now the time to move M D F  cut and past it to new path location

now the time to move L D F  cut and past it to new path location

Step 6:

now time to bring database online

after success message kindly refresh on server as see below

you can see Test db Growth db comes online as see below

Step 7:

also kindly verify the M D F and L D F path comes to new location as see below

Note:

1)If you rename  M D F or L D F but not give correct name in alter query 

2) if you alter query with new path location but not move M D F and L D F then you will get below error

if we any data file or log file not place correctly we could see following error

path not available to come online 

still show db in recovery pending cant come online due to path not available

you can see take online is disabled you should only take to offline mode only 

solution

if db was in recovery pending then check the data file path m d f and l d f available  then 

once again alter query with correct path and bring db offline and bring online

that is it keep blogging and reading ☺☺☺☺☺☺☺☺