SQL Server

SQL Server 中服务器之间的所有数据迁移步骤

页面创建日期 :

环境

SQL Server
  • SQL Server 2019
  • SQL Server 2012

数据迁移方法及其优缺点

有几种方法可以批量将 SQL Server 数据库中的数据迁移到其他服务器上的 SQL Server： 每种方法在迁移过程中都有优点和缺点，因此请根据您的环境选择最佳方法。

迁移方法 服务器之间的网络连接 迁移到早期版本迁移时文件大小表布局的预同步身份验证
备份和还原数据库 不需要（移动文件） 不可以 普通 无需（自动完全更换） 不需要
将数据输出为 SQL 不需要（移动文件） 是 大的 需要或删除目标表 不需要
使用工具中的“导入和导出数据” 必需 是 无 取决于迁移设置 需要

先决条件

  • 假定 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 Native Client 11.0
服务器名称 (local) 如果是其他服务器或指定了实例名称，请输入该值。
认证 Windows 身份验证
数据库 SampleDatabase 输入实际从中迁移的数据库的名称

接下来，设置目标数据库的连接设置。 您正在使用 SQL Server 身份验证，因为该身份验证位于其他服务器上。

参数名称 设定值 备注
数据源 SQL Server Native Client 11.0
服务器名称 目标服务器名称（+ = 实例名称）
认证 SQL Server 身份验证
数据库 SampleDatabase 输入要实际迁移到的数据库的名称

选中“从一个或多个表或视图复制数据”，然后单击“下一步”按钮。

选择要迁移的表。 此列表还会显示视图，因此不要检查视图。

如果源表和目标表名称相同，则会自动选择目标表。 如果列名称相同，则会自动设置列名称映射。

以下是设置中需要注意的事项：

  • 如果主键是自动编号，则必须选中“编辑映射”和“允许插入 ID”，否则迁移将失败。
  • 如果目标没有表，则会创建一个表
  • 如果目标表没有相同的列名称，则不会迁移它。 不要按原样迁移或更改目标列。
  • 默认情况下，将添加记录。 如果要替换记录，请在目标上删除记录，或从“编辑映射”中选中“删除目标表中的行”。

实际上，您必须考虑上述内容，但在此提示中，目标没有数据，并且没有自动编号，因此无需设置任何内容即可继续。

确保选中“立即运行”，然后单击“下一步”。

单击“完成”。

数据迁移开始。 完成所有迁移后，关闭它。

通过检查目标数据库，可以验证是否已创建不存在的表。 您还可以确保现有表没有增加列。

您可以验证数据是否已迁移。 表 2 中的备注列未映射，因此尚未迁移。

使用 SQL 服务器身份验证连接到 SQL 服务器的服务器端设置