SQL Server 中伺服器之間的所有數據遷移步驟

環境

SQL Server

• SQL Server 2019
• SQL Server 2012

數據遷移方法及其優缺點

有幾種方法可以批量將 SQL Server 資料庫中的數據遷移到其他伺服器上的 SQL Server： 每種方法在遷移過程中都有優點和缺點，因此請根據您的環境選擇最佳方法。

先決條件

• 假定 SQL 伺服器和 SQL 伺服器管理工作室安裝在每台伺服器上。
• 此提示使 SQL 伺服器版本不同。

創建範例數據

在描述資料遷移時，請在以下表設定的資料庫中執行此操作：

數據源

SQL Server 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 伺服器管理工作室。 為 SQL Server 版本選擇資料夾。

登錄到目標伺服器。 使用 SQL 伺服器管理工作室的 PC 在其他 PC 上都很好，但此提示中的所有內容都在其自己的伺服器上工作。

從物件資源管理員中右鍵按下目標資料庫，然後在“任務”下選擇“備份”。

刪除右下角備份目標清單中初始顯示的備份路徑。

刪除後，按下「添加」按鈕。

按兩下「更改路徑」 按鈕。

由於「備份」資料夾最初處於選取狀態，因此請在下面的檔名中輸入要備份的檔名，然後按下「確定」 按鈕。 檔名是可選的，因此請確保名稱易於理解。

按兩下確定按鈕。

確保已添加到清單中，然後按下確定按鈕。

備份檔案已建立在以下資料夾中，因此請將其帶到目標伺服器： 資料夾路徑因 SQL Server 版本和實例名稱而異，因此請及時檢查。

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

複製到目標伺服器后的狀態。 由於將其放在 SQL Server 備份資料夾中更容易載入，因此我將其放在以下資料夾中： 這也會因 SQL Server 版本和實例名稱而異，因此請及時檢查。

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

在目標伺服器上啟動 SQL 伺服器管理工作室。 在這裡，您登錄到自己的伺服器。

從物件資源管理器中右鍵按兩下目標資料庫，然後選擇“任務”、“還原”和“資料庫”。

確保在左側頁面選擇中選擇了「常規」，從右側的「源」中選擇「設備」，然後按下右側的瀏覽按鈕。

按兩下「添加」按鈕。

選擇您帶來的檔。

從左側頁面選擇「選項」，然後選中以下兩個選項。 選中這兩個複選框可強制替換其他系統，即使它們連接到資料庫也是如此。

• 覆蓋現有資料庫
• 關閉與目標資料庫的現有連接

但是，請注意，如果在選中「關閉與目標資料庫的現有連接」的情況下還原失敗，則原始資料庫可能不可用。

選中后，按兩下確定按鈕。

您可以驗證表設定和記錄是否已完全遷移。

順便說一下，當您嘗試將新版本的 SQL Server 資料庫還原到舊版本的 SQL Server 時，您會收到以下錯誤： 使用此方法進行數據遷移時，基本版本或新版本的遷移是基本遷移。

如何將數據列印為 SQL（文稿）並在目標資料庫上運行

如何將表中的所有記錄列印為插入語句。 由於 SQL 語句幾乎相同，因此，無論 SQL Server 版本如何，它都可以應用於其他類型的資料庫。

但是，由於記錄將寫入插入語句，因此檔大小會增加。 因此，當記錄數增加時，它變得毫無用處，因此，當記錄數量較少時，可以使用該方法。

此外，SQL 是針對源表生成的，因此，如果與目標表配置不同，則不可用。 建立表也可以包含在 SQL 中，但在這種情況下，您必須在目標中刪除該表。

步驟

首先，在源中啟動 SQL 伺服器管理工作室。 為 SQL Server 版本選擇資料夾。

登錄到目標伺服器。 使用 SQL 伺服器管理工作室的 PC 在其他 PC 上都很好，但此提示中的所有內容都在其自己的伺服器上工作。

右鍵按下目標資料庫，然後選擇“任務”和“生成腳本”。

按兩下一步“。

您可以選擇遷移哪些表中的數據。 在此處選擇所有內容或選擇特定表。

由於輸出是 SQL，因此您可以選擇「檔案」、「剪貼板」或「在視窗中顯示」。 選擇后，按兩下右上角的「高級」 按鈕。

從常規組中的“生成腳本的數據類型”中選擇“僅數據”。

然後，選擇“下一步”進行確認。 然後，您可以看到 SQL 生成。 您只需在目標上運行此 SQL 即可。

由於這隻是插入語句，因此，如果要使數據相同，則必須在目標上刪除所有記錄。

遷移表配置

如果要遷移表配置，請將前面的“生成腳本的數據類型”選項設置為“架構和數據”。

這將生成表創建腳本，如下所示： 如果要使用它，請刪除目標表，然後運行它。

如果資料庫也包含在其中，則還會生成資料庫創建腳本。

如何使用導入和匯出數據遷移數據

安裝 SQL Server 時，還會安裝名為「導入和匯出數據」 的工具。 這允許您將表中的數據遷移到其他伺服器。

此工具的遷移的好處是，您可以將數據遷移到早期版本的 SQL Server。 它有點難以使用，但即使有大約 100 萬個數據，它也可以以幾分鐘的級別遷移，因此非常適合大型數據遷移。 但是，請注意，源伺服器和目標伺服器必須通過專用網路連接，並且網路不能在完全不同的伺服器之間使用。

先決條件

生成 SQL Server 身份驗證或 Windows 身份驗證，以便使用此工具的 PC 可以存取每個 SQL Server。

此提示使用來自源伺服器的工具。

步驟

從「開始」功能表中選擇「導入和匯出數據」。 它包含在 SQL Server 資料夾中，但資料夾名稱因 SQL Server 版本而異，因此請及時查找。

按兩下一步“。

選擇要從中遷移的資料庫。 這一次，它在源伺服器上運行，因此可以通過 Windows 身份驗證。

接下來，設置目標資料庫的連接設置。 您正在使用 SQL Server 身份驗證，因為該身份驗證位於其他伺服器上。

選中「從一個或多個表或檢視複製數據」，然後按下一步按鈕。

選擇要遷移的表。 此清單還會顯示檢視，因此不要檢查檢視。

如果源表和目標表名稱相同，則會自動選擇目標表。 如果列名稱相同，則會自動設置列名稱映射。

以下是設置中需要注意的事項：

• 如果主鍵是自動編號，則必須選中「編輯映射」和」允許插入ID“，否則遷移將失敗。
• 如果目標沒有表，則會創建一個表
• 如果目標表沒有相同的列名稱，則不會遷移它。 不要按原樣遷移或更改目標列。
• 默認情況下，將添加記錄。 如果要替換記錄，請在目標上刪除記錄，或從“編輯映射”中選中“刪除目標表中的行”。

實際上，您必須考慮上述內容，但在此提示中，目標沒有數據，並且沒有自動編號，因此無需設置任何內容即可繼續。

確保選中「立即運行」，然後按兩下一步。

按兩下“完成”。

數據遷移開始。 完成所有遷移后，關閉它。

通過檢查目標資料庫，可以驗證是否已創建不存在的表。 您還可以確保現有表沒有增加列。

您可以驗證資料是否已遷移。 表 2 中的備註列未映射，因此尚未遷移。