如何将 SQL Azure 数据库复制到本地开发服务器?

2021-12-06 00:00:00 azure-sql-database azure database-migration sql-server

有谁知道如何将 SQL Azure 数据库复制到我的开发机器上?我想停止为在云中拥有开发数据库而付费，但这是获取生产数据的最佳方式.我将我的生产数据库复制到一个新的开发数据库，​​但我希望在本地拥有相同的数据库.

Does anyone know how I can copy a SQL Azure database to my development machine? I'd like to stop paying to have a development database in the cloud, but it's the best way to get production data. I copy my production database to a new development database but I'd like to have that same database local.

有什么建议吗?

推荐答案

有多种方法可以做到这一点:

There are multiple ways to do this:

  1. 使用 SSIS(SQL Server 集成服务).它只在您的表中导入 data.不会传输列属性、约束、键、索引、存储过程、触发器、安全设置、用户、登录等.然而，这是一个非常简单的过程，只需通过 SQL Server Management Studio 中的向导即可完成.
  2. 结合使用SSIS 和数据库创建脚本.这将为您提供数据和所有未由 SSIS 传输的丢失元数据.这也很简单.首先使用 SSIS 传输数据(参见下面的说明)，然后从 SQL Azure 数据库创建 DB Create 脚本，并在本地数据库上重新播放.
  3. 最后，您可以使用SQL Azure 中的导入/导出服务.这会将数据(带有架构对象)作为 BACPAC 传输到 Azure Blob 存储.您将需要一个 Azure 存储帐户并在 Azure Web 门户中执行此操作.选择要导出的数据库时，只需在 Azure Web 门户中按导出"按钮即可.缺点是它只是手动程序，我不知道有什么方法可以通过工具或脚本自动执行此操作——至少是需要点击网页的第一部分.
  1. Using SSIS (SQL Server Integration Services). It only imports data in your table. Column properties, constraints, keys, indices, stored procedures, triggers, security settings, users, logons, etc. are not transferred. However it is very simple process and can be done simply by going through wizard in SQL Server Management Studio.
  2. Using combination of SSIS and DB creation scripts. This will get you data and all missing metadata that is not transferred by SSIS. This is also very simple. First transfer data using SSIS (see instructions below), then create DB Create script from SQL Azure database, and re-play it on your local database.
  3. Finally, you can use Import/Export service in SQL Azure. This transfers data (with a schema objects) to Azure Blob Storage as a BACPAC. You will need an Azure Storage account and do this in Azure web portal. It is as simple as pressing an "Export" button in the Azure web portal when you select the database you want to export. The downside is that it is only manual procedure, I don't know a way to automate this through tools or scripts -- at least the first part that requires a click on the web page.

方法 #1(使用 SSIS)的手动程序如下:

Manual procedure for method #1 (using SSIS) is the following:

  • 在 Sql Server Management Studio (SSMS) 中，在本地 SQL 实例上创建新的空数据库.
  • 从上下文菜单中选择导入数据(右键单击数据库 -> 任务 -> 导入数据...)
  • 输入源 (SQL Azure) 的连接参数.选择.Net Framework Data Provider for SqlServer"作为提供者.
  • 选择现有的空本地数据库作为目标.
  • 按照向导操作——您将能够选择要复制的表数据.您可以选择跳过任何不需要的表.例如.如果您将应用程序日志保存在数据库中，则您的备份中可能不需要它.

您可以通过创建 SSIS 包并在您想重新导入数据的任何时候重新执行它来自动化它.请注意，您只能使用 SSIS 导入到干净的数据库，一旦您已经完成一次，您就无法对本地数据库进行增量更新.

You can automate it by creating SSIS package and re-executing it any time you like to re-import the data. Note that you can only import using SSIS to a clean DB, you cannot do incremental updates to your local database once you already done it once.

方法#2(SSID 数据加架构对象)非常简单.首先完成上述步骤，然后创建数据库创建脚本(在 SSMS 中右键单击数据库，选择生成脚本 -> 数据库创建).然后在您的本地数据库上重新播放此脚本.

Method #2 (SSID data plus schema objects) is very simple. First go though a steps described above, then create DB Creation script (righ click on database in SSMS, choose Generate Scripts -> Database Create). Then re-play this script on your local database.

方法 #3 在此处的博客中进行了描述:http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/.有一个视频剪辑，其中包含将 DB 内容作为 BACPAC 传输到 Azure Blob 存储的过程.之后，您可以在本地复制该文件并将其导入您的 SQL 实例.此处描述了将 BACPAC 导入数据层应用程序的过程:http://msdn.microsoft.com/en-us/library/hh710052.aspx.

Method #3 is described in the Blog here: http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/. There is a video clip with the process of transferring DB contents to Azure Blob storage as BACPAC. After that you can copy the file locally and import it to your SQL instance. Process of importing BACPAC to Data-Tier application is described here: http://msdn.microsoft.com/en-us/library/hh710052.aspx.

相关文章