none
Moving Datafiles in SQL Server RRS feed

  • Question

  • HI All,

      We have SQL 2008 R2 in our setup.  We are facing space issues on the server as existing Drive size can't be increased due to some reasons. Now we are planning to move the datafiles, logfiles to new separate drives.  We have 3-7 datafiles and 1 log file for each DB. 

      Here We wanted to move all log files to one Drive and Datafiles to another Drive. does this activity requires any downtime? Please suggest us different options we can have,

    Regards,

    Varun

    Thursday, December 31, 2015 4:38 AM

Answers

  • Good Day,

    Yes to move the data and log file , the database requires downtime. Below is a simple example for database test. Hope this help you- thanks

    --STEP1) FIND THE NAMES AND PHYSICAL LOCATION FOR THE DATA AND LOG FILES FOR THE PARTICULAR DATABASE.
    
    SELECT NAME, PHYSICAL_NAME FROM
    SYS.MASTER_FILES;
    
     --STEP2) SET THE DATABASE OFFLINE BY THE FOLLOWING QUERY:-
    
     ALTER DATABASE TEST
     SET OFFLINE
    
     --OR
    ALTER DATABASE TEST
     SET OFFLINE WITH ROLLBACK IMMEDIATE
    
     --STEP3) MODIFY THE FILE LOCATION AS BELOW:-
    
      --FOR DATA FILES
    
    ALTER DATABASE TEST 
    MODIFY FILE
     (
     NAME =TEST_DATA,
     FILENAME='P:\DATAFILES\TEST_DATAFILE.MDF' -- NEW LOCATION 
    )
     --FOR LOG FILES
    
    ALTER DATABASE TEST 
    MODIFY FILE
     (
     NAME =TEST_LOG,
     FILENAME='R:\LOGFILES\TEST_LOGFILE.LDF'  --NEW LOCATION
     )
    
    -- STEP4) SET THE DATABASE ONLINE
    
     ALTER DATABASE TEST
     SET ONLINE;



    Thursday, December 31, 2015 6:37 AM

All replies

  • There are several options to move a database, each one has its pros and cons.  Most of case it requires downtime.

    Options to Move a Big SQL Server Database to a New Drive with Minimal Downtime

    Thursday, December 31, 2015 5:12 AM
  • Good Day,

    Yes to move the data and log file , the database requires downtime. Below is a simple example for database test. Hope this help you- thanks

    --STEP1) FIND THE NAMES AND PHYSICAL LOCATION FOR THE DATA AND LOG FILES FOR THE PARTICULAR DATABASE.
    
    SELECT NAME, PHYSICAL_NAME FROM
    SYS.MASTER_FILES;
    
     --STEP2) SET THE DATABASE OFFLINE BY THE FOLLOWING QUERY:-
    
     ALTER DATABASE TEST
     SET OFFLINE
    
     --OR
    ALTER DATABASE TEST
     SET OFFLINE WITH ROLLBACK IMMEDIATE
    
     --STEP3) MODIFY THE FILE LOCATION AS BELOW:-
    
      --FOR DATA FILES
    
    ALTER DATABASE TEST 
    MODIFY FILE
     (
     NAME =TEST_DATA,
     FILENAME='P:\DATAFILES\TEST_DATAFILE.MDF' -- NEW LOCATION 
    )
     --FOR LOG FILES
    
    ALTER DATABASE TEST 
    MODIFY FILE
     (
     NAME =TEST_LOG,
     FILENAME='R:\LOGFILES\TEST_LOGFILE.LDF'  --NEW LOCATION
     )
    
    -- STEP4) SET THE DATABASE ONLINE
    
     ALTER DATABASE TEST
     SET ONLINE;



    Thursday, December 31, 2015 6:37 AM