วิธีการโยกย้ายตำแหน่งของ Data File และ Log File ของฐานข้อมูล MSSQL

สำหรับการใช้งานโปรแกรมฐานข้อมูล Microsoft SQL นั้น ในบางครั้งเราอาจไม่ต้องการที่จะเก็บ Data File (.mdf) และ Log File (.ldf) ของฐานข้อมูลที่เราใช้อยู่เก็บไว้ที่ตำแหน่งของค่าเริ่มต้นของโปรแกรม ซึ่งตามปกติแล้วตัวโปรแกรมจะตั้งค่าพื้นฐานไว้ที่ C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
ซึ่งจะเป็น Drive เดียวกันกับ Windows อาจส่งผลให้เวลาที่ก้อน Database มีขนาดใหญ่ขึ้น จะส่งผลต่อการทำงานโดยรวมของเครื่องคอมพิวเตอร์แม่ข่าย
ดังนั้นจึงจะขอแยกเป็น 2 กรณีในการแก้ไขตำแหน่งของไฟล์ Database คือกรณีที่เราจะใช้คำสั่ง Create Database แล้วให้ไฟล์ไปยังค่า Default โดยอัตโนมัติ และกรณีที่เราสร้าง Database ไปแล้ว และต้องการที่จะย้ายตำแหน่งของไฟล์

ตั้งค่าพื้นฐานของตำแหน่งที่จะเก็บ Data File, Log File และ Backup File ของ Database ในกรณีที่จะสร้างขึ้นใหม่

• ให้เปิดโปรแกรม Microsoft SQL Server Management Studio ในการ Connect ไปที่ Database Server ที่เราต้องการ จากนั้นให้คลิกขวาที่ Database Server ที่เราเชื่อมต่อไป และเลือก Properties

• จากนั้นให้เลือก Tab ไปที่ Database Setting ในหน้านี้เราจะสามารถแก้ไข Database default locations ของ Data File, Log File และ Backup File ที่เราต้องการจะเก็บได้ ซึ่งทุกครั้งที่เราทำการ Create Database หรือ Restore Database ตัวโปรแกรมจะใช้ค่าตำแหน่ง Default นี้ในการสร้างไฟล์ .mdf และ .ldf

ย้าย Database ที่มีการสร้างไว้แล้ว ไปไว้ยังตำแหน่งอื่นๆ

• ในกรณีที่สร้าง Database ไปแล้ว และอยู่ที่ Default Path นั้น การจะย้าย Data File และ Log File สมมติว่าเรามี Database ที่ชื่อ TestDB อยู่จำเป็นที่จะต้องตัด Connect ทั้งหมดออกก่อน โดยการคลิกขวาที่ Database ที่เราต้องการจะย้าย และเลือก "Take Offline" และให้รอจนกว่าจะขึ้นสถานะ Success

• จากนั้นให้ใช้คำสั่ง Query ต่อไปนี้

USE MASTER;
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'
GO

• ถ้าหลังจาก Run Query แล้วไม่พบ Error ใดๆ เราจึงจะสามารถย้าย Data File และ Log File ไปไว้ยังที่ๆเราต้องการได้
• หลังจากย้ายเสร็จแล้ว เราจะทำการสร้าง Database ขึ้นมาใหม่ โดยให้ไปอ้างถึงตำแหน่งของ Data File และ Log File ที่เราย้ายไป

CREATE DATABASE [TestDB] ON
( FILENAME = N'D:\loc2\TestDB.mdf' ),
( FILENAME = N'D:\loc2\TestDB_log.ldf' )
FOR ATTACH
GO

• ในกรณีที่ย้ายมาที่ใหม่ อาจเกิดกรณีที่ใช้คำสั่งนี้แล้วตัว Database จะอยู่ในสถานะ Read Only เนื่องจากโปรแกรม Microsoft SQL Server ไม่มีสิทธิ์ในการเข้าถึง Directory ใหม่ที่เราย้ายได้ย้ายไป ซึ่งโปรแกรมจะใช้ User MSSQLSERVER ในการเข้าถึงไฟล์นั้นๆ ดังนั้นเราจึงจำเป็นต้องเพิ่มสิทธิ์ลงไปใน Folder นั้นๆก่อน
• ให้เราคลิกขวาของ Folder ที่เราต้องการจะเพิ่มสิทธิ์เข้าไป เลือก Properties จากนั้นเลือกไปที่ Tab Security ==> Edit ==> Add จากนั้นให้เราค้นหา User โดยใช้คำว่า NT SERVICE\MSSQLSERVER จากนั้นกด Check Names
• จากนั้นให้เลือกไปที่ MSSQLSERVER กด OK

• เมื่อได้ User นี้แล้ว จึงให้สิทธิ์ Full Control เพื่อให้ User MSSQLSERVER สามารถแก้ไขไฟล์ใน Directory นี้ได้

Reference
[1] http://blog.sqlauthority.com/2012/10/28/sql-server-move-database-files-mdf-and-ldf-to-another-location/
[2] https://msdn.microsoft.com/en-us/library/jj219062.aspx