How to Move SQL Server database files to other drive ?

Leave a comment (0) Go to comments

This question is being asked by one of the training participants. As per the his scenario, Initially this database server was set up and all database data files are being hosted on drive G:\  and transaction log was on F:\.

Now the drive, F:\ is full and there are some IO contentions as well, so he wanted move a particular database from a existing drive to a new drive.

I told the steps but he asked me the step by step procedure, so I promised him that I am going to write a short note on this.

Moving databases could be required because of

  • Running out of space in the current disk drive.
  • Moving to a faster disk (e.g. moving from SATA to SSD).
  • Replacing the current disk drive and many more

Here is step by step procedure, how you can move you database / database files from a exiting drive to a new drive.

Step 1. Run the following script to get the database file list

USE TEST;  -- please replace "TEST" with your database name  
    GO  
    SELECT   
        DB_NAME(database_id) AS Database_Name,  
        name AS Logical_Name,  
        physical_name AS Physical_Name,  
        type_desc AS Type_Description  
    FROM sys.master_files WHERE database_id = DB_ID()  
    GO

Step 2. Run the following script to take the database offline


USE master;  
    GO  
    ALTER DATABASE TEST SET OFFLINE;   -- please replace "TEST" with your database name  
    GO

Step 3. Manually move the database file(s) listed in Step 1 to the new location

Step 4. Run the following script for each file that was moved

USE master;  
    GO  
    ALTER DATABASE TEST MODIFY FILE (NAME = TEST_Data, FILENAME = 'C:\newlocation\TEST_Data.mdf';  -- please replace AdventureWorks2008R2_Data with your database file name, and replace "C:\newlocation\TEST_Data.mdf" with the new file location in your environment.  
    GO 

Step 5. Run the following script to take the database online.

USE master;  
    GO  
    ALTER DATABASE TEST SET ONLINE;  -- please replace AdventureWorks2008R2 with your database name.  
    GO

Step 6. Run the script in Step 1 again to verify the database file list


EOF - How to Move SQL Server database files to other drive ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.