Thứ Hai, 12 tháng 12, 2011

How to Move SQL Server database files to other drive ?

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  

Không có nhận xét nào:

Đăng nhận xét