使用其他方法导入和导出 SQL Server 数据
接下来,您可以找到有关使用快照将 Microsoft SQL Server 数据导入到 Amazon RDS 中的信息。还可以找到有关使用快照从运行 SQL Server 的 RDS 数据库实例中导出数据的信息。
如果您的方案支持此方法,则可使用本机备份和还原功能更轻松地将数据移入和移出 Amazon RDS。有关更多信息,请参阅 使用本机备份和还原导入和导出 SQL Server 数据库。
注意
Amazon RDS for Microsoft SQL Server 不支持将数据导入 msdb
数据库。
使用快照将数据导入 RDS for SQL Server
使用快照将数据导入 SQL Server 数据库实例
-
创建数据库实例。有关更多信息,请参阅“创建 Amazon RDS 数据库实例”。
-
阻止应用程序访问目标数据库实例。
如果您在导入数据时阻止应用程序访问数据库实例,数据传输速度会更快。此外,您无需担心在加载数据时会因其他应用程序无法写入到数据库实例而引起冲突。如果因出现问题而必须要回滚到之前的数据库快照,则丢失的唯一更改就是导入的数据。在解决问题后,您可以再次导入这些数据。
有关控制数据库实例访问权限的信息,请参阅 使用安全组控制访问权限。
-
创建目标数据库快照。
如果目标数据库已存有数据,我们建议您先为数据库创建快照,然后再导入数据。如果数据导入过程出现问题或您想要放弃更改,您可以使用此快照将数据库还原到先前的状态。有关数据库快照的信息,请参阅 为单可用区数据库实例创建数据库快照。
注意
获取数据库快照时,数据库的 I/O 操作会在备份进行期间暂停一会(以毫秒为单位)。
-
禁用目标数据库的自动备份。
禁用目标数据库实例上的自动备份将会提高导入数据时的性能,因为 Amazon RDS 在禁用自动备份后不会记录事务。但需要考虑以下问题。执行时间点恢复时需要使用自动备份。因此,在导入数据时,无法将数据库还原到特定时间点。此外,除非您选择保留先前在数据库实例上创建的所有自动备份,否则系统会将其清除。
选择保留自动备份有助于防止意外删除数据。Amazon RDS 还会保存数据库实例属性以及每个自动备份以便进行恢复。利用此选项,您可以将已删除的数据库实例还原到备份保留期内的指定时间点,即使在删除它之后也是如此。由于自动备份是用于有效数据库实例的,它们将在特定备份时段结束后自动被删除。
您也可使用先前的快照来恢复数据库,并且您创建的所有快照仍然可用。有关自动备份的信息,请参阅 备份简介。
-
禁用外键约束 (如适用)。
如果需要禁用外键约束,可以使用以下脚本。
--Disable foreign keys on all tables DECLARE @table_name SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@table_name)+' NOCHECK CONSTRAINT ALL'; EXEC (@cmd); FETCH NEXT FROM table_cursor INTO @table_name; END CLOSE table_cursor; DEALLOCATE table_cursor; GO
-
删除索引 (如果适用)。
-
禁用触发器 (如果适用)。
如果您需要禁用触发器,则可使用以下脚本执行该操作。
--Disable triggers on all tables DECLARE @enable BIT = 0; DECLARE @trigger SYSNAME; DECLARE @table SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name, table_object.name table_name FROM sysobjects trigger_object JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id WHERE trigger_object.type = 'TR'; OPEN trigger_cursor; FETCH NEXT FROM trigger_cursor INTO @trigger, @table; WHILE @@FETCH_STATUS = 0 BEGIN IF @enable = 1 SET @cmd = 'ENABLE '; ELSE SET @cmd = 'DISABLE '; SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' '; EXEC (@cmd); FETCH NEXT FROM trigger_cursor INTO @trigger, @table; END CLOSE trigger_cursor; DEALLOCATE trigger_cursor; GO
-
查询源 SQL Server 实例,获取要导入到目标数据库实例的所有登录名。
SQL Server 将登录名和密码存储在
master
数据库中。由于 Amazon RDS 未授予访问master
数据库的权限,因此您无法直接将登录名和密码导入目标数据库实例。您必须在源 SQL Server 实例上查询master
数据库以生成数据定义语言 (DDL) 文件。此文件应包括要添加到目标数据库实例的所有登录名和密码。此文件还应包括要传输的角色成员资格和权限。有关查询
master
数据库的信息,请参阅 Microsoft 知识库中的 Transfer logins and passwords between instances of SQL Server。 该脚本输出是可在目标数据库实例上运行的另一个脚本。知识库文章中的脚本包含以下代码:
p.type IN
在
p.type
出现的所有位置,改用以下代码:p.type = 'S'
-
使用导入数据中的方法导入数据。
-
授予应用程序访问目标数据库实例的权限。
完成数据导入后,您可以向导入期间被阻止的应用程序授予访问数据库实例的权限。有关控制数据库实例访问权限的信息,请参阅 使用安全组控制访问权限。
-
启用目标数据库实例的自动备份。
有关自动备份的信息,请参阅 备份简介。
-
启用外键约束。
如果您之前禁用了外键约束,现在可通过以下脚本重新启用。
--Enable foreign keys on all tables DECLARE @table_name SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@table_name)+' CHECK CONSTRAINT ALL'; EXEC (@cmd); FETCH NEXT FROM table_cursor INTO @table_name; END CLOSE table_cursor; DEALLOCATE table_cursor;
-
启用索引 (如果适用)。
-
启用触发器 (如果适用)。
如果您之前禁用了触发器,现在可以通过以下脚本启用触发器。
--Enable triggers on all tables DECLARE @enable BIT = 1; DECLARE @trigger SYSNAME; DECLARE @table SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name, table_object.name table_name FROM sysobjects trigger_object JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id WHERE trigger_object.type = 'TR'; OPEN trigger_cursor; FETCH NEXT FROM trigger_cursor INTO @trigger, @table; WHILE @@FETCH_STATUS = 0 BEGIN IF @enable = 1 SET @cmd = 'ENABLE '; ELSE SET @cmd = 'DISABLE '; SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' '; EXEC (@cmd); FETCH NEXT FROM trigger_cursor INTO @trigger, @table; END CLOSE trigger_cursor; DEALLOCATE trigger_cursor;
导入数据
Microsoft SQL Server Management Studio 是一种包含在所有 Microsoft SQL Server 版本 (Express 版除外) 中的图像化 SQL Server 客户端。您可以从 Microsoft 上免费下载 SQL Server Management Studio Express。要找到此下载,请访问 Microsoft 网站
注意
SQL Server Management Studio 应用程序只能在 Windows 环境中运行。
SQL Server Management Studio 包括能够帮助将数据导入 SQL Server 数据库实例的以下工具:
-
生成和发布脚本向导
-
导入和导出向导
-
批量复制
生成和发布脚本向导
生成和发布脚本向导可创建一个包含数据库架构或数据本身,或兼具两者的脚本。您可以在本地 SQL Server 部署中为数据库生成脚本。然后,您可以运行该脚本以将其包含的信息传输到 Amazon RDS 数据库实例。
注意
对于 1GiB 或更大的数据库,只编写数据库架构的脚本会更高效。然后,您可以使用导入和导出向导,或 SQL Server 的批量复制功能来传输数据。
有关生成和发布脚本向导的详细信息,请参阅 Microsoft SQL Server 文档
在此向导中,请特别注意设置脚本选项页面中的高级选项,确保已选中脚本需要包含的所有选项。例如,默认情况下,脚本不包含数据库触发器。
生成并保存脚本后,您可以使用 SQL Server Management Studio 连接数据库实例,然后运行脚本。
导入和导出向导
导入和导出向导工具可创建一种特殊的集成服务包,您可以使用此集成服务包将数据从本地 SQL Server 数据库复制到目标数据库实例。此向导可筛选要将哪些表格甚至是表格内的哪些元组复制到目标数据库实例。
注意
导入和导出向导在用于大型数据集时非常有效,但是就从本地部署中远程导出数据而言,这可能不是最快的方式。要采用更快的方式,请考虑 SQL Server 批量复制功能。
有关导入和导出向导的详细信息,请参阅 Microsoft SQL Server 文档
在此向导中,请在 Choose a Destination (选择目标) 页面上执行以下操作:
-
对于 Server Name (服务器名称),键入数据库实例的终端节点名称。
-
对于服务器身份验证模式,请选择 Use SQL Server Authentication。
-
对于 User name (用户名) 和 Password (密码),请键入您为数据库实例创建的主用户凭证。
批量复制
SQL Server 批量复制功能是一种将数据从源数据库复制到数据库实例的高效工具。批量复制可将指定的数据写入一个数据文件,如 ASCII 文件。然后您可以再次运行批量复制,将此文件内容写入目标数据库实例。
本部分使用了 bcp 实用工具,所有版本的 SQL Server 都包含此工具。有关批量导入和导出操作的详细信息,请参阅 Microsoft SQL Server 文档
注意
使用批量复制前,您必须先将数据库架构导入目标数据库实例。本主题前面介绍的生成和发布脚本向导这一工具非常适合用于该操作。
以下命令用于连接到本地 SQL Server 实例。它在您现有 SQL Server 部署的 C:\ 根目录中生成指定表的制表符定界文件。指定表时需使用完全限定名,文本文件的名称与复制的表相同。
bcp
dbname
.schema_name
.table_name
out C:\table_name
.txt -n -S localhost -Uusername
-Ppassword
-b 10000
上述代码包含以下选项:
-
-n
指定,批量复制将使用待复制数据的本机数据类型。 -
-S
指定 bcp 实用工具连接到的 SQL Server 实例。 -
-U
指定要登录到 SQL Server 实例的账户的用户名。 -
-P
为 指定的用户指定密码。-U
-
-b
指定每批导入数据的行数。
注意
可能有对导入情况来说很重要的其他参数。例如,您可能需要与身份值有关的 -E
参数。有关更多信息,请参阅 Microsoft SQL Server 文档
例如,假设某一数据库名为 store
,使用了默认架构 dbo
且包含一个名为 customers
的表。密码为 admin
的用户账户 insecure
将 customers
表中的 10000 行复制到名为 customers.txt
的文件中。
bcp store.dbo.customers out C:\customers.txt -n -S localhost -U admin -P insecure -b 10000
生成数据文件后,可以使用类似的命令将数据上传到数据库实例。事先在目标数据库实例上创建数据库和架构。然后使用 in
参数指定输入文件,而不是使用 out
指定输出文件。您需要指定数据库实例的终端节点,而不是使用 localhost 指定本地 SQL Server 实例。如果您使用的端口不是 1433 端口,也需要执行该指定操作。用户名和密码是数据库实例的主用户名和密码。语法如下所示。
bcp
dbname
.schema_name
.table_name
in C:\table_name
.txt -n -Sendpoint
,port
-Umaster_user_name
-Pmaster_user_password
-b 10000
为继续上述示例,我们假设主用户名是 admin
,密码为 insecure
。数据库实例的终端节点是 rds.ckz2kqd4qsn1.us-east-1.rds.amazonaws.com
,且您使用端口 4080。命令如下所示。
bcp store.dbo.customers in C:\customers.txt -n -S rds.ckz2kqd4qsn1.us-east-1.rds.amazonaws.com,4080 -U admin -P insecure -b 10000
注意
作为安全最佳实践,请指定除此处所示提示以外的密码。
从 RDS for SQL Server 中导出数据
您可以选择以下选项之一来从 RDS for SQL Server 数据库实例中导出数据:
-
使用完整备份文件 (.bak) 的本机数据库备份 – 使用 .bak 文件备份数据库的过程已得到大幅优化,因此,它通常是最快的导出数据的方法。有关更多信息,请参阅“使用本机备份和还原导入和导出 SQL Server 数据库”。
-
SQL Server 导入和导出向导 – 有关更多信息,请参阅 SQL Server 导入和导出向导。
-
SQL Server 生成和发布脚本向导与 bcp 实用工具 – 有关更多信息,请参阅 SQL Server 生成和发布脚本向导与 bcp 实用工具。
SQL Server 导入和导出向导
您可以使用 SQL Server 导入和导出向导将一个或多个表、视图或查询从 RDS for SQL Server 数据库实例复制到另一个数据存储。如果目标数据存储不是 SQL Server,这将是最佳选择。有关更多信息,请参阅 SQL Server 文档中的 SQL Server 导入和导出向导
SQL Server 导入和导出向导是 Microsoft SQL Server Management Studio 的一部分。此图形化 SQL Server 客户端包含在所有 Microsoft SQL Server 版本(Express 版除外)中。SQL Server Management Studio 应用程序只能在 Windows 环境中运行。您可以从 Microsoft 上免费下载 SQL Server Management Studio Express。要找到此下载,请访问 Microsoft 网站
使用 SQL Server 导入和导出向导来导出数据
-
在 SQL Server Management Studio 中,连接到 RDS for SQL Server 数据库实例。有关如何执行该操作的详细信息,请参阅连接到运行 Microsoft SQL Server 数据库引擎的数据库实例。
-
在 Object Explorer 中,展开 Databases (数据库),打开源数据库的上下文(右键单击)菜单,选择 Tasks (任务),然后选择 Export Data (导出数据)。此时将显示向导。
-
在 Choose a Data Source (选择数据源) 页面上,执行以下操作:
-
对于 Data source (数据源),选择
SQL Server Native Client 11.0
。 -
确认服务器名称框中显示 RDS for SQL Server 数据库实例的终端节点。
-
选择 Use SQL Server Authentication (使用 SQL Server 身份验证)。对于 用户名 和 密码,键入您的数据库实例的主用户名和密码。
-
确认 Database (数据库) 框中显示要从中导出数据的数据库。
-
选择下一步。
-
-
在 Choose a Destination (选择目标) 页面上,执行以下操作:
-
对于 Destination (目标),选择
SQL Server Native Client 11.0
。注意
提供了其他目标数据源。这些包括 .NET Framework 数据提供程序、OLE DB 提供程序、SQL Server Native Client 提供程序、ADO.NET 提供程序、Microsoft Office Excel、Microsoft Office Access 以及平面文件源。如果您选择这些数据源之一作为目标,请跳过步骤 4 的其余部分。有关接下来要提供的连接信息的详细信息,请参阅 SQL Server 文档中的选择目标
。 -
对于 Server name (服务器名称),键入目标 SQL Server 数据库实例的服务器名称。
-
选择适当的身份验证类型。如有必要,键入用户名和密码。
-
对于 Database (数据库),选择目标数据库的名称,或选择 New (新建) 以创建一个包含已导出数据的新数据库。
如果选择新建,请参阅 SQL Server 文档中的创建数据库
,以了解要提供的数据库信息。 -
选择下一步。
-
-
在 Table Copy or Query (表复制或查询) 页面上,选择 Copy data from one or more tables or views (从一个或多个表或视图复制数据) 或 Write a query to specify the data to transfer (编写查询以指定要传输的数据)。选择下一步。
-
如果您选择 Write a query to specify the data to transfer (编写查询以指定要传输的数据),则将看到 Provide a Source Query (提供源查询) 页面。键入或贴入 SQL 查询,然后选择 Parse (分析) 以进行验证。在验证查询后,选择 Next (下一步)。
-
在 Select Source Tables and Views (选择源表和视图) 页面上,执行以下操作:
-
选择要导出的表和视图,或确认已选择您提供的查询。
-
选择 Edit Mappings (编辑映射) 并指定数据库和列映射信息。有关详细信息,请参阅 SQL Server 文档中的列映射
。 -
(可选) 若要查看要导出的数据的预览,请选择表、视图或查询,然后选择 Preview。
-
选择下一步。
-
-
在 Run Package (运行程序包) 页面上,确认已选择 Run immediately (立即运行)。选择下一步。
-
在 Complete the Wizard (完成向导) 页面上,确认数据导出详细信息是预期的详细信息。选择 Finish (结束)。
-
在 The execution was successful (执行已成功) 页面上,单击 Close (关闭)。
SQL Server 生成和发布脚本向导与 bcp 实用工具
您可以使用 SQL Server 生成和发布脚本向导来为整个数据库或仅为所选对象创建脚本。可在目标 SQL Server 数据库实例上运行这些脚本以重新创建已编写脚本的对象。然后,可以使用 bcp 实用工具将选定对象的数据批量导出到目标数据库实例。若要在两个 SQL Server 数据库实例之间移动整个数据库 (包括表以外的对象) 或大量数据,这将是最佳选择。有关 bcp 命令行语法的完整说明,请参阅 Microsoft SQL Server 文档中的 bcp 实用工具
SQL Server 生成和发布脚本向导是 Microsoft SQL Server Management Studio 的一部分。此图形化 SQL Server 客户端包含在所有 Microsoft SQL Server 版本(Express 版除外)中。SQL Server Management Studio 应用程序只能在 Windows 环境中运行。您可以从 Microsoft 上免费下载
使用 SQL Server 生成和发布脚本向导与 bcp 实用工具来导出数据
-
在 SQL Server Management Studio 中,连接到 RDS for SQL Server 数据库实例。有关如何执行该操作的详细信息,请参阅 连接到运行 Microsoft SQL Server 数据库引擎的数据库实例。
-
在 Object Explorer 中,展开 Databases (数据库) 节点并选择要编写脚本的数据库。
-
按照 SQL Server 文档中的生成和发布脚本向导
中的说明,创建脚本文件。 -
在 SQL Server Management Studio 中,连接到目标 SQL Server 数据库实例。
-
在 Object Explorer(对象资源管理器)中选择目标 SQL Server 数据库实例后,在 File(文件)菜单上选择 Open(打开),选择 File(文件),然后打开脚本文件。
-
如果您已编写了整个数据库的脚本,请查看脚本中的 CREATE DATABASE 语句。请确保将在所需位置使用所需参数创建数据库。有关更多信息,请参阅 SQL Server 文档中的 CREATE DATABASE
。 -
如果在脚本中创建数据库用户,请检查目标数据库实例中是否有这些用户的服务器登录名。如果没有登录名,请为这些用户创建登录名;否则,用于创建数据库用户的脚本命令将失败。有关更多信息,请参阅 SQL Server 文档中的创建登录名
。 -
选择 SQL 编辑器菜单上的 !Execute 以运行脚本文件并创建数据库对象。完成脚本后,验证是否存在预期的所有数据库对象。
-
使用 bcp 实用工具将数据从 RDS for SQL Server 数据库实例导出到文件。打开命令提示符并键入以下命令。
bcp database_name.schema_name.table_name out data_file -n -S aws_rds_sql_endpoint -U username -P password
上述代码包含以下选项:
-
table_name 是您已在目标数据库中重新创建且现在需要填充数据的其中一个表的名称。
-
data_file 是要创建的数据文件的完整路径和名称。
-
-n
指定,批量复制将使用待复制数据的本机数据类型。 -
-S
指定要从中导出数据的 SQL Server 数据库实例。 -
-U
指定在连接到 SQL Server 数据库实例时要使用的用户名。 -
-P
为-U
指定的用户指定密码。
下面显示了示例命令。
bcp world.dbo.city out C:\Users\JohnDoe\city.dat -n -S sql-jdoe.1234abcd.us-west-2.rds.amazonaws.com,1433 -U JohnDoe -P ClearTextPassword
重复此步骤,直到您获得要导出的所有表的数据文件。
-
-
按照 SQL Server 文档中的批量导入数据基本指南
中的说明,准备目标数据库实例以便批量导入数据。 -
在考虑 SQL Server 文档中的关于批量导入和批量导出操作
中讨论的性能和其他问题后,决定要使用的批量导入方法。 -
从使用 bcp 实用工具创建的数据文件中批量导入数据。为此,请按照 SQL Server 文档中的使用 bcp 实用工具导入和导出批量数据
或使用 BULK INSERT 或 OPENROWSET(BULK...) 导入批量数据 中的说明进行操作,具体取决于您在步骤 11 中所做的决定。