SQL Server: ย้ายฐานข้อมูลไปเครื่องใหม่ด้วย Backup/Restore

SELECT @@VERSION;
SELECT name, compatibility_level, recovery_model_desc, is_encrypted FROM sys.databases;
SELECT name, type_desc, default_database_name FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') AND name NOT LIKE '##%';
SELECT name, enabled FROM msdb.dbo.sysjobs;

BACKUP DATABASE [AppDB] TO DISK = N'D:\Backup\AppDB_full.bak'
WITH CHECKSUM, COMPRESSION, STATS = 10;
RESTORE VERIFYONLY FROM DISK = N'D:\Backup\AppDB_full.bak' WITH CHECKSUM;

robocopy \\OLDSQL01\D$\Backup E:\Restore AppDB_full.bak /J /R:1 /W:1
icacls E:\Restore /grant "NT Service\MSSQLSERVER:(OI)(CI)RX"

RESTORE FILELISTONLY FROM DISK = N'E:\Restore\AppDB_full.bak';

RESTORE DATABASE [AppDB] FROM DISK = N'E:\Restore\AppDB_full.bak'
WITH MOVE N'AppDB' TO N'E:\Data\AppDB.mdf',
     MOVE N'AppDB_log' TO N'F:\Log\AppDB_log.ldf',
     NORECOVERY, STATS = 10;

ขั้นตอนที่ 5: หยุดแอปแล้วปิดท้ายด้วย Differential Backup

ในช่วงเวลาที่นัดไว้ ให้หยุดแอปทั้งหมดก่อน แล้วทำบนเครื่องเดิม

ALTER DATABASE [AppDB] SET READ_ONLY WITH ROLLBACK IMMEDIATE;
BACKUP DATABASE [AppDB] TO DISK = N'D:\Backup\AppDB_diff.bak'
WITH DIFFERENTIAL, CHECKSUM, STATS = 10;

คัดลอกไฟล์ diff ไปเครื่องใหม่แบบเดียวกับขั้นตอนที่ 3 แล้ว Restore พร้อมเปิดใช้ฐานข้อมูล

RESTORE DATABASE [AppDB] FROM DISK = N'E:\Restore\AppDB_diff.bak'
WITH RECOVERY, STATS = 10;
ALTER DATABASE [AppDB] SET READ_WRITE;

สถานะอ่านอย่างเดียวถูกบันทึกมากับไฟล์ backup จึงต้องสั่ง READ_WRITE บนเครื่องใหม่ ส่วนเครื่องเดิมให้คงสถานะอ่านอย่างเดียวไว้ ถ้าต้องย้อนกลับ แค่สั่ง ALTER DATABASE [AppDB] SET READ_WRITE; บนเครื่องเดิมแล้วชี้แอปกลับ

ขั้นตอนที่ 6: สร้าง Login และแก้ Orphaned Users

ผู้ใช้ในฐานข้อมูลผูกกับ Login ด้วย SID ไม่ใช่ชื่อ Login แบบ SQL ที่สร้างใหม่บนเครื่องใหม่จะได้ SID ใหม่ ผู้ใช้ในฐานข้อมูลจึงกลายเป็น Orphaned User และแอปล็อกอินไม่ผ่าน สร้าง Login ด้วยชื่อเดิม แล้วหาผู้ใช้ที่หลุดการผูก

CREATE LOGIN [appuser] WITH PASSWORD = N'Use-A-Long-Random-Password-2026';
USE [AppDB];
SELECT dp.name AS orphaned_user
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL AND dp.type = 'S' AND dp.authentication_type_desc = 'INSTANCE';

ผูกผู้ใช้แต่ละคนกลับเข้ากับ Login และตั้งเจ้าของฐานข้อมูลใหม่ เพราะเจ้าของเดิมอาจเป็นบัญชีที่ไม่มีบนเครื่องนี้

ALTER USER [appuser] WITH LOGIN = [appuser];
ALTER AUTHORIZATION ON DATABASE::[AppDB] TO [sa];

ถ้าต้องการให้รหัสผ่านเดิมใช้ได้ทันทีโดยไม่ต้องแจ้งทุกระบบ ให้ใช้สคริปต์ sp_help_revlogin ที่ Microsoft เผยแพร่ ซึ่งสร้าง Login พร้อม SID และ hash ของรหัสผ่านเดิม Login แบบ Windows ของโดเมนเดียวกันไม่มีปัญหานี้เพราะใช้ SID ของโดเมน

ขั้นตอนที่ 7: ตรวจความถูกต้องและตั้ง Compatibility Level

DBCC CHECKDB (N'AppDB') WITH NO_INFOMSGS;
USE [AppDB];
EXEC sp_updatestats;
SELECT name, compatibility_level FROM sys.databases WHERE name = N'AppDB';

หลัง Restore ลงรุ่นใหม่ ฐานข้อมูลจะคงระดับเดิมไว้ เช่น 130 ของรุ่น 2016 ซึ่งช่วยให้แอปทำงานเหมือนเดิม ให้ใช้งานจริงด้วยระดับเดิมก่อน เปิด Query Store เก็บประวัติแผนการทำงาน แล้วค่อยขยับเป็นระดับของรุ่นใหม่ เช่น 160 ของ SQL Server 2022 เมื่อทดสอบแล้ว

ALTER DATABASE [AppDB] SET QUERY_STORE = ON;
ALTER DATABASE [AppDB] SET COMPATIBILITY_LEVEL = 160;

ถ้า Query ใดช้าลงหลังเปลี่ยนระดับ ให้ดูใน Query Store แล้วบังคับใช้แผนเดิม หรือปรับระดับกลับชั่วคราว จากนั้นเปลี่ยน connection string ของแอป และสร้างงาน Agent ใหม่จากสคริปต์ที่ส่งออกจากเครื่องเดิมด้วยเมนู Script Job as › CREATE To

ปัญหาที่พบบ่อยเกี่ยวกับการย้าย SQL Server

Restore แล้วขึ้น Msg 3169 ว่าไฟล์มาจากเวอร์ชันที่เข้ากันไม่ได้ แก้อย่างไร?

เครื่องปลายทางเป็นรุ่นเก่ากว่าเครื่องที่ทำ backup ซึ่งไม่รองรับ ต้องติดตั้งรุ่นเดียวกันหรือใหม่กว่า ถ้าจำเป็นต้องลงรุ่นเก่าจริง ต้องย้ายด้วยการสคริปต์โครงสร้างและข้อมูลแทนการ Restore

ย้ายเสร็จแล้วแอปขึ้น Login failed for user ทำไม?

มักเป็นเพราะยังไม่ได้สร้าง Login บนเครื่องใหม่ หรือสร้างแล้วแต่ยังไม่ได้ผูกกับผู้ใช้ในฐานข้อมูล ให้ทำขั้นตอนที่ 6 ให้ครบ และตรวจว่า default database ของ Login ชี้ไปที่ฐานข้อมูลที่มีอยู่จริง ข้อความใน error log ของเครื่องใหม่จะบอกเหตุผลละเอียดกว่าที่แอปเห็น

Restore ลงรุ่น Express แล้วขึ้นว่าเกิน licensed limit of 10240 MB หมายความว่าอะไร?

ฐานข้อมูลใหญ่เกินขีดจำกัด 10 GB ของรุ่น Express ซึ่งไม่มีวิธีปลดล็อก ต้องใช้ Edition ที่สูงกว่า เช่น Standard หรือลดขนาดข้อมูลบนเครื่องเดิมก่อนทำ backup

ฐานข้อมูลเปิด TDE ไว้ Restore แล้วขึ้น Cannot find server certificate ต้องทำอย่างไร?

ไฟล์ backup ถูกเข้ารหัสด้วยใบรับรองที่อยู่ในฐานข้อมูล master ของเครื่องเดิม ให้สำรองใบรับรองพร้อม private key ด้วย BACKUP CERTIFICATE บนเครื่องเดิม แล้วสร้างกลับบนเครื่องใหม่ด้วย CREATE CERTIFICATE ... FROM FILE ก่อน Restore ถ้าไม่มีใบรับรองนี้ ข้อมูลจะกู้คืนไม่ได้เลย

ถ้าต้องการให้ช่วยวางแผนย้ายฐานข้อมูลขนาดใหญ่หรือหลายฐานข้อมูลพร้อมกัน ติดต่อทีมงาน THAI DATA CLOUD หรือดูเครื่องปลายทางที่ Cloud Windows Server รายละเอียดเพิ่มเติมอ่านได้จาก Back up and restore ใน Microsoft Learn, ALTER USER และ ALTER DATABASE compatibility level

สรุปขั้นตอน SQL Server

ทบทวน SQL Server ทั้งหมดตามลำดับ ก่อนนำไปใช้กับระบบจริง

1. สำรวจเครื่องเดิม
2. ทำ Full Backup และตรวจไฟล์
3. คัดลอกไฟล์และให้สิทธิ์โฟลเดอร์
4. Restore แบบ NORECOVERY รอไว้
5. หยุดแอปแล้วปิดท้ายด้วย Differential Backup
6. สร้าง Login และแก้ Orphaned Users
7. ตรวจความถูกต้องและตั้ง Compatibility Level

เมื่อทำครบทุกข้อแล้ว SQL Server จะพร้อมใช้งาน หากผลลัพธ์ไม่เป็นไปตามที่คาด ให้ย้อนกลับไปดูหัวข้อปัญหาที่พบบ่อยด้านบน ซึ่งรวบรวมกรณีที่พบจริงจากการใช้งาน SQL Server ไว้แล้ว

อ่านเพิ่มเติมเกี่ยวกับ SQL Server

• คู่มือ Linux Server ของ Ubuntu
• เชื่อมต่อฐานข้อมูลบนเซิร์ฟเวอร์ผ่าน SSH Tunnel ด้วย DBeaver
• คู่มือการใช้งานทั้งหมดของ THAI DATA CLOUD