How to Move Database Files (M D F & L D F ) From One Drive to Another on SQL Server and How to solve Recovery Pending Issue

How to Move Database Files (M D F & L D F ) From One Drive to Another on SQL Server


Step 1. we need to take it and note down existing file path 

it is very important to know db name and logical name and physical name to recover 

in future

Step 2. we need to alter with new path  (Make sure db name,logical name,physical name correct)

Step 3. we need to set db single user and back to Multi User to eliminate existing connections

Step 4. we need to take db offline

Step 5: we need to move m d f and l d f to new path

Step 6: we need to make db online

Step 7: we need to verify location through s p _h e l p d b 

Demo:

Step 1. we need to take it and note down existing file path 

Method 1 :

To view Database files through Query

-- shows info for all databases
SELECT sd.name as DBName, saf.name as LogicalName, saf.filename
FROM master.sys.sysaltfiles saf 
INNER JOIN master.sys.sysdatabases sd ON saf.dbid = sd.dbid

-- shows file info for the current database
SELECT * FROM sys.database_files

Method 2 :

To view Database files through Query

Exec SP_HELPDB [TestMoveMDFLDF]

Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases  sd on sm.database_id=sd.database_id
WHERE sd.name='TestMoveMDFLDF'

Step 2. we need to alter with new path (Make sure db name,logical name,physical name correct)

Sample format

USE master;
GO
ALTER DATABASE dbname
MODIFY FILE (NAME = dblogicalnameMDF, FILENAME = 'D:\Source\dbphysicalnameMDF.mdf');
GO
ALTER DATABASE dbname
MODIFY FILE (NAME = dblogicalnameLDF, FILENAME = 'D:\TempDB Source\dbphysicalnameLDF.ldf');



USE master;
GO
ALTER DATABASE TestMoveMDFLDF
MODIFY FILE (NAME = TestMoveMDFLDF, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012CONVTEST\MSSQL\Data\TestMoveMDFLDF.MDF');
GO

ALTER DATABASE TestMoveMDFLDF

MODIFY FILE (NAME = TestMoveMDFLDF_log, FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012CONVTEST\MSSQL\Data\TestMoveMDFLDF.LDF');

Step 3. we need to set db single user and back to Multi User to eliminate existing connections


you can see still some existing connections are using DB. it will allow to make offline





so we need to issue set single user command to eliminate existing connections on db

USE master

ALTER DATABASE [TestMoveMDFLDF]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE [TestMoveMDFLDF]

SET MULTI_USER WITH ROLLBACK IMMEDIATE

Now you can see there is no transaction on database

so now we can bring mulit user 

 because you can't set offline when db on single user with some users connected to it

 Step 4. we need to take db offline

Step 5: we need to move m d f and l d f to new path

you can see this is the old path we need to copy/cut Data files from old path

then paste into the new path as we already altered as see below

Step 6: we need to make db online

Step 7: we need to verify location through s p _h e l p d b 


Exec SP_HELPDB [TestMoveMDFLDF]


Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases  sd on sm.database_id=sd.database_id
WHERE sd.name='TestMoveMDFLDF'

That s it we done it

let see some problems

if we did not proper update alter query like logical name and physical path we would get problem as see below

above picture you can see we did not update properly on transaction log file we updated same with primary date file

so if you try to bring online it will give error as see below

so database will be went to Recovery pending status

so be careful on Logical name and Physical path and Physical Name while you move

resolution

after we set correct location and  Logical name and Physical path(verified File moved correct path) and Physical Name