ขั้นตอนในการย้ายข้อมูลทั้งหมดระหว่างเซิร์ฟเวอร์ใน SQL Server

สิ่งแวดล้อม

เซิร์ฟเวอร์ SQL

• SQL Server 2019
• เซิร์ฟเวอร์ SQL 2012

วิธีการย้ายข้อมูลและข้อดีและข้อเสียของแต่ละ

การย้ายข้อมูลฐานข้อมูล SQL Server ไปยัง SQL Server บนเซิร์ฟเวอร์อื่นเป็นกลุ่มสามารถทําได้หลายวิธี ดังนี้ แต่ละคนมีข้อดีและข้อเสียในระหว่างการย้ายข้อมูลดังนั้นเลือกวิธีการที่เหมาะสมกับสภาพแวดล้อมของคุณมากที่สุด

เงื่อนไขเบื้องต้น

• สมมติว่ามีการติดตั้ง SQL Server และ SQL Server Management Studio บนแต่ละเซิร์ฟเวอร์
• เคล็ดลับนี้กล้าที่จะทําให้เวอร์ชันเซิร์ฟเวอร์ SQL แตกต่างกัน

สร้างข้อมูลตัวอย่าง

เมื่อพูดถึงการย้ายข้อมูล คุณจะทําสิ่งนั้นในฐานข้อมูลด้วยการกําหนดค่าตารางต่อไปนี้

แหล่งข้อมูลการย้ายข้อมูล

เซิร์ฟเวอร์ SQL 2012

• ตาราง 1 (คอลัมน์:คีย์, ค่า)
• ตารางที่ 2 (คอลัมน์:คีย์ ค่า หมายเหตุ)
• ตาราง 3 (คอลัมน์:คีย์, ค่า)

ต่อไปนี้เป็นสคริปต์การสร้างฐานข้อมูล เปลี่ยนเส้นทางในเวลาที่เหมาะสม

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ด้านล่างนี้เป็นสคริปต์การสร้างตาราง

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
	[備考] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル2] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル3](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル3] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ต่อไปนี้เป็นสคริปต์เรกคอร์ดเพิ่มเติม:

USE [SampleDatabase]
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (1, N'1aaa')
GO
INSERT [dbo].[テーブル1] ([キー], [値]) VALUES (2, N'1bbb')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (1, N'2aaa', N'2remarks')
GO
INSERT [dbo].[テーブル2] ([キー], [値], [備考]) VALUES (2, N'2bbb', N'2remarks')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (1, N'3aaa')
GO
INSERT [dbo].[テーブル3] ([キー], [値]) VALUES (2, N'3bbb')
GO

การย้ายข้อมูลไปยัง

SQL Server 2019

• ตาราง 1 (คอลัมน์:คีย์, ค่า)
• ตาราง 2 (คอลัมน์:คีย์, ค่า)

ระเบียนในแต่ละตารางว่างเปล่า

ต่อไปนี้เป็นสคริปต์การสร้างฐานข้อมูล เปลี่ยนเส้นทางในเวลาที่เหมาะสม

USE [master]
GO

CREATE DATABASE [SampleDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SampleDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SampleDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\SampleDatabase_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

ด้านล่างนี้เป็นสคริปต์การสร้างตาราง

USE [SampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル1](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブル1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[テーブル2](
	[キー] [int] NOT NULL,
	[値] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[キー] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

วิธีการย้ายข้อมูลสํารองและคืนค่าฐานข้อมูล

วิธีนี้คือการสํารองฐานข้อมูลทั้งหมดเป็นแฟ้มบนเซิร์ฟเวอร์ต้นทาง ดังนั้น การกําหนดค่าตารางทั้งหมดจะถูกตั้งค่าเป็นปลายทาง ซึ่งทั้งหมดจะเหมือนกับต้นทาง

นอกจากนี้ วิธีนี้ยังไม่อนุญาตให้คุณโยกย้ายจาก SQL Server รุ่นที่ใหม่กว่าไปยัง SQL Server รุ่นที่เก่ากว่า (หากความแตกต่างของเวอร์ชันมีขนาดเล็กอาจเป็นไปได้))

ขั้นตอน

เริ่ม SQL Server Management Studio จากแหล่งข้อมูล เลือกโฟลเดอร์ที่ตรงกับรุ่น SQL Server ของคุณ

ล็อกอินไปยังเซิร์ฟเวอร์เป้าหมาย พีซีที่ใช้ SQL Server Management Studio นั้นใช้ได้บนพีซีเครื่องอื่น แต่เคล็ดลับนี้ช่วยให้คุณทํางานบนเซิร์ฟเวอร์ของคุณเองได้

จาก Object Explorer ให้คลิกขวาที่ฐานข้อมูล แล้วเลือก

ลบเส้นทางการสํารองข้อมูลเริ่มต้นในรายการปลายทางการสํารองข้อมูลที่มุมล่างขวา

เมื่อคุณลบออก ให้คลิกปุ่ม เพิ่ม

คลิกปุ่ม เปลี่ยนเส้นทาง

ฉันคิดว่าโฟลเดอร์ "Backup" จะถูกเลือกโดยค่าเริ่มต้นดังนั้นป้อนชื่อไฟล์เพื่อสํารองในชื่อไฟล์ด้านล่างและคลิกปุ่มตกลง ชื่อไฟล์เป็นตัวเลือกดังนั้นทําให้เข้าใจง่าย

คลิกปุ่ม ตกลง

ตรวจสอบว่าได้เพิ่มรายการนั้นลงในรายการแล้ว แล้วคลิกปุ่ม ตกลง

เนื่องจากแฟ้มสํารองถูกสร้างขึ้นในโฟลเดอร์ต่อไปนี้ เส้นทางโฟลเดอร์ขึ้นอยู่กับรุ่นของ SQL Server และชื่อของอินสแตนซ์ ดังนั้นโปรดตรวจสอบในเวลาที่เหมาะสม

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

สถานะหลังจากคัดลอกไปยังเซิร์ฟเวอร์ปลายทาง โดยจะอยู่ในโฟลเดอร์ต่อไปนี้ เนื่องจากง่ายต่อการโหลดถ้าคุณใส่ไว้ในโฟลเดอร์ Backup ของ SQL Server นอกจากนี้ยังขึ้นอยู่กับรุ่นของ SQL Server และชื่ออินสแตนซ์ดังนั้นโปรดตรวจสอบในเวลาที่เหมาะสม

C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\Backup

เริ่ม SQL Server Management Studio บนเซิร์ฟเวอร์ที่คุณกําลังย้ายข้อมูลไป คุณเข้าสู่ระบบเซิร์ฟเวอร์ของคุณที่นี่

จาก Object Explorer ให้คลิกขวาที่ฐานข้อมูลปลายทาง แล้วเลือก งาน การคืนค่า และ ฐานข้อมูล

ตรวจสอบให้แน่ใจว่าเลือกทั่วไปในการเลือกหน้าซ้ายเลือก "อุปกรณ์" จาก "แหล่งที่มา" ทางด้านขวาและคลิกปุ่มเรียกดูทางด้านขวา

คลิกปุ่ม เพิ่ม

เลือกไฟล์ที่คุณนํามา

เลือก "ตัวเลือก" จากการเลือกหน้าซ้ายและตรวจสอบสองหน้าต่อไปนี้ ถ้าคุณตรวจสอบสองสิ่งนี้ คุณสามารถบังคับให้ระบบอื่นสลับในขณะที่เชื่อมต่อกับฐานข้อมูลได้

• เขียนทับฐานข้อมูลที่มีอยู่
• ปิดการเชื่อมต่อที่มีอยู่ไปยังฐานข้อมูลปลายทาง

อย่างไรก็ตาม โปรดทราบว่าถ้าการคืนค่าล้มเหลวด้วยการตรวจสอบ "ปิดการเชื่อมต่อที่มีอยู่ไปยังฐานข้อมูลปลายทาง"

เมื่อคุณเลือก ให้คลิกปุ่ม ตกลง

คุณสามารถตรวจสอบว่าการตั้งค่าคอนฟิกตารางและเรกคอร์ดถูกโยกย้ายแล้วทั้งหมดแล้ว

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

วิธีการ: เอาท์พุทข้อมูลเป็น SQL (สคริปต์) และเรียกใช้บนฐานข้อมูลปลายทาง

นี่คือวิธีการแสดงผลระเบียนทั้งหมดในตารางเป็นคําสั่งแทรก คําสั่ง SQL เกือบจะเหมือนกัน จึงสามารถนําไปใช้กับฐานข้อมูลชนิดอื่นได้ในบางกรณี

อย่างไรก็ตามเนื่องจากระเบียนถูกเขียนขึ้นเพื่อแทรกประโยคทีละประโยคขนาดไฟล์จะเพิ่มขึ้น ดังนั้นเมื่อจํานวนระเบียนเพิ่มขึ้นจึงไร้ประโยชน์ดังนั้นจึงเป็นวิธีการที่สามารถใช้งานได้เมื่อจํานวนน้อย

นอกจากนี้ยังสร้าง SQL สําหรับตารางที่ย้ายตารางดังนั้นจึงไม่พร้อมใช้งานถ้าแตกต่างจากการกําหนดค่าตารางที่ย้าย คุณยังสามารถรวมการสร้างตารางใน SQL ได้ แต่คุณต้องลบตารางหนึ่งครั้งณ ปลายทาง

ขั้นตอน

เริ่ม SQL Server Management Studio จากแหล่งข้อมูล เลือกโฟลเดอร์ที่ตรงกับรุ่น SQL Server ของคุณ

ล็อกอินไปยังเซิร์ฟเวอร์เป้าหมาย พีซีที่ใช้ SQL Server Management Studio นั้นใช้ได้บนพีซีเครื่องอื่น แต่เคล็ดลับนี้ช่วยให้คุณทํางานบนเซิร์ฟเวอร์ของคุณเองได้

คลิกขวาที่ฐานข้อมูลเป้าหมายและเลือก "งาน" และ "สร้างสคริปต์"

คลิก ถัดไป

คุณสามารถเลือกตารางที่จะย้ายข้อมูลไปได้ เลือกทั้งหมดหรือเลือกตารางเฉพาะที่นี่

เนื่องจากผลลัพธ์เป็น SQL คุณสามารถเลือก "ไฟล์", "คลิปบอร์ด" หรือ "แสดงในหน้าต่าง" เมื่อเลือกแล้วให้คลิกปุ่ม "ขั้นสูง" ที่ด้านบนขวา

เลือกข้อมูลจาก "ชนิดของข้อมูลที่จะสร้างสคริปต์" ในกลุ่ม ทั่วไป เท่านั้น

หลังจากนั้นเลือก "ถัดไป" เพื่อยืนยัน คุณจะเห็นว่า SQL ถูกสร้างขึ้นแล้ว สิ่งที่เหลืออยู่คือการเรียกใช้ SQL นี้บนปลายทาง

เนื่องจากเป็นเพียงคําสั่งแทรกหากคุณต้องการให้ข้อมูลเหมือนกันคุณต้องลบระเบียนทั้งหมดที่ปลายทางล่วงหน้า

ย้ายการตั้งค่าคอนฟิกตารางด้วย

ถ้าคุณต้องการย้ายการตั้งค่าคอนฟิกตารางด้วย ให้ตั้งค่าการเลือก "ชนิดของข้อมูลเพื่อสร้างสคริปต์" ก่อนหน้านี้เป็น Schema และข้อมูล

สคริปต์การสร้างตารางยังถูกสร้างขึ้นดังนี้: ถ้าคุณต้องการใช้สิ่งนี้ จะดําเนินการหลังจากลบตารางที่จะย้าย

นอกจากนี้ยังสร้างสคริปต์การสร้างฐานข้อมูลถ้ามันรวมอยู่ด้วย

วิธีการย้ายข้อมูลโดยใช้ 'การนําเข้าและส่งออกข้อมูล'

เมื่อคุณติดตั้ง SQL Server คุณจะติดตั้งเครื่องมือที่ชื่อว่า นําเข้าและส่งออกข้อมูล ด้วย คุณสามารถใช้ข้อมูลนั้นเพื่อโยกย้ายข้อมูลจากตารางไปยังเซิร์ฟเวอร์อื่นได้

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

เงื่อนไขเบื้องต้น

สร้างการรับรองความถูกต้องของ SQL Server หรือการรับรองความถูกต้องของ Windows เพื่อให้คุณสามารถเข้าถึง SQL Server แต่ละตัวจากพีซีที่ใช้เครื่องมือนี้

เคล็ดลับนี้ใช้เครื่องมือนี้จากเซิร์ฟเวอร์ที่ย้ายมา

ขั้นตอน

เลือก นําเข้าและส่งออกข้อมูล จากเมนู 'เริ่ม' ชื่อโฟลเดอร์นี้ถูกรวมอยู่ในโฟลเดอร์ใน SQL Server แต่ชื่อโฟลเดอร์จะแตกต่างกันไปตามรุ่นของ SQL Server ดังนั้นให้ค้นหาในโฟลเดอร์นั้นในเวลาที่เหมาะสม

คลิก ถัดไป

เลือกฐานข้อมูลที่คุณต้องการโยกย้าย คราวนี้กําลังทํางานอยู่บนเซิร์ฟเวอร์ต้นทาง คุณจึงสามารถผ่านการรับรองความถูกต้องของ Windows ได้

จากนั้น ให้ตั้งค่าการเชื่อมต่อสําหรับฐานข้อมูลปลายทาง การรับรองความถูกต้องของ Sql Server กําลังถูกใช้เนื่องจากอยู่บนเซิร์ฟเวอร์อื่น

เลือก "คัดลอกข้อมูลจากตารางหรือมุมมองอย่างน้อยหนึ่งรายการ" แล้วคลิกปุ่มถัดไป

เลือกตารางที่คุณต้องการโยกย้าย รายการนี้ยังแสดงมุมมองดังนั้นอย่าตรวจสอบ

ถ้าชื่อตารางต้นทางและปลายทางเหมือนกัน นอกจากนี้ยังตั้งค่าการแม็ปของชื่อคอลัมน์โดยอัตโนมัติถ้าชื่อคอลัมน์เหมือนกัน

สิ่งที่ควรทราบมีดังนี้

• ถ้าคีย์หลักถูกนับอัตโนมัติ การโยกย้ายจะล้มเหลวถ้าคุณไม่เลือก "อนุญาตให้แทรก ID" จาก "แก้ไขการแมป"
• ถ้าไม่มีตารางที่จะโยกย้ายไป
• ถ้าตารางปลายทางไม่มีชื่อคอลัมน์เดียวกัน ไม่ต้องย้ายหรือเปลี่ยนแปลงคอลัมน์ปลายทางต่อไป
• เรกคอร์ดจะถูกเพิ่มตามค่าเริ่มต้น ถ้าคุณต้องการแทนที่ ให้ลบระเบียนที่ปลายทางล่วงหน้า หรือเลือก "ลบแถวในตารางปลายทาง" จาก "แก้ไขการแมป"

ในความเป็นจริงคุณต้องพิจารณาข้างต้น แต่ในเคล็ดลับนี้ไม่มีข้อมูลที่ปลายทางและไม่มีการกําหนดหมายเลขอัตโนมัติดังนั้นคุณสามารถดําเนินการต่อได้โดยไม่ต้องตั้งค่าอะไรเลย

ตรวจสอบให้แน่ใจว่าได้เลือก "เรียกใช้เดี๋ยวนี้" แล้วคลิก "ถัดไป"

คลิก เสร็จสิ้น

การย้ายข้อมูลจะเริ่มขึ้น ปิดเมื่อการโยกย้ายทั้งหมดเสร็จสมบูรณ์

ถ้าคุณดูที่ฐานข้อมูลเป้าหมาย คุณสามารถตรวจสอบว่าตารางที่ไม่มีอยู่ถูกสร้างขึ้นแล้ว คุณยังสามารถเห็นได้ว่าไม่มีคอลัมน์สําหรับตารางที่มีอยู่อีกต่อไป

คุณสามารถตรวจสอบว่าข้อมูลกําลังถูกโยกย้ายอยู่ คอลัมน์หมายเหตุในตาราง 2 ไม่ได้ถูกแมปและยังไม่ได้ถูกโยกย้าย