How to relocate or move user Database in same instance - SQL SERVER 2005/2008

Move or Relocate Database  (Data file and Log file) of User Database in same instance:

Step 1: Run the command to find the present path of DB

SELECT name, physical_name

FROM sys.master_files where name like '%AdventureWorksDW%'

Step 2: Bring Database Offline.

  
alter database AdventureWorksDW set offline with rollback immediate ;
 

Step 3: Copy/Cut Paste the physical files.

Copy/Cut the Data file and log file  from Source:

Paste to Destination( new path or location):

Step 4: Run the below command to locate in new path for mdf.

alter database AdventureWorksDW

modify file (name=AdventureWorksDW_Data , filename='E:\SQL2005\Instance2\AdventureWorksDW_Data.mdf');

Step 5: Run the below command to locate in new path for ldf.

       (same for ndf also with proper logical name and extension)

alter database AdventureWorksDW

modify file (name=AdventureWorksDW_Log , filename='E:\SQL2005\Instance2\AdventureWorksDW_log.ldf');

Step 6: Validate the new location.

SELECT name, physical_name

FROM sys.master_files where name like '%AdventureWorksDW%'

Step 7: Bring Database Online

alter database AdventureWorksDW set online;

This procedure I follow in Production Database. Downtime require only for the Database which you will do the activity and the Downtime of that Database depends on its size.

For any query regarding Move or Relocate Database  (Data file and Log file) of User Database in same instance, please email me : rizwan2007ali@gmail.com