DBA- how to move/ change the dat base file paths in sql server.

 

Note: The new path should have all permissions to the service account (which is used in SQL Server services) 

 Step1:  run the ALTER command for a specified database with new path locations. 

--ALTER DATABASE [mydb]  

--MODIFY FILE ( NAME = mydb,  

--FILENAME = N'G:Data\mydb.mdf'); 

--GO

 

--ALTER DATABASE [mydb]  

--MODIFY FILE ( NAME = mydb_log,  

--FILENAME = N'G:Log\mydb_log.ldf');

--GO

 

Step 2:  Take the above database offline 

Step3: Move the data and log file to a new folder from an old location -Cut /paste

 Step 4: Online the above database