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
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() GOStep 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