如何将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.

有人知道我如何将SQL Azure数据库复制到我的开发机器上吗?我不想花钱在云中建立一个开发数据库，但这是获取生产数据的最佳方式。我将生产数据库复制到一个新的开发数据库，但是我希望在本地拥有相同的数据库。

Any suggestions?

有什么建议吗?

22 个解决方案

#1

There are multiple ways to do this:

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

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. 使用SSIS (SQL Server Integration Services)。它只导入表中的数据。不传输列属性、约束、键、索引、存储过程、触发器、安全设置、用户、登录等。不过，这是一个非常简单的过程，只需在SQL Server Management Studio中通过向导即可完成。
3. 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.
4. 使用SSIS和DB创建脚本的组合。这将获取数据和SSIS未传输的所有丢失的元数据。这也很简单。首先使用SSIS传输数据(参见下面的说明)，然后从SQL Azure数据库创建DB创建脚本，然后在本地数据库上重新播放。
5. 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.
6. 最后，您可以在SQL Azure中使用导入/导出服务。这将数据(带有模式对象)作为BACPAC传输到Azure Blob存储。您将需要一个Azure存储帐户，并在Azure web门户中进行此操作。当您选择要导出的数据库时，就像在Azure web门户中按下“导出”按钮一样简单。缺点是，它只是手动的过程，我不知道如何通过工具或脚本实现自动化——至少第一部分需要在web页面上单击。

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

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

• In Sql Server Management Studio (SSMS) create new empty database on your local SQL instance.
• 在Sql Server Management Studio (SSMS)中，在本地Sql实例上创建新的空数据库。
• Choose Import Data from context menu (right click the database -> Tasks -> Import data...)
• 从上下文菜单中选择导入数据(右键单击数据库->任务->导入数据…)
• Type in connection parameters for the source (SQL Azure). Select ".Net Framework Data Provider for SqlServer" as a provider.
• 输入源的连接参数(SQL Azure)。选择”。SqlServer的Net框架数据提供者“作为提供者”。
• Choose existing empty local database as destination.
• 选择现有的空本地数据库作为目标。
• Follow the wizard -- you will be able to select tables data you want to copy. You can choose to skip any of the tables you don't need. E.g. if you keep application logs in database, you probably don't need it in your backup.
• 跟随向导——您将能够选择要复制的表数据。您可以选择跳过任何不需要的表。例如，如果您将应用程序日志保存在数据库中，那么您在备份时可能不需要它。

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.

您可以通过创建SSIS包并在任何需要重新导入数据的时候重新执行它来实现自动化。注意，您只能使用SSIS将数据导入到一个干净的DB中，一旦您完成了一次，就不能对本地数据库进行增量更新。

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.

方法#2 (SSID数据和模式对象)非常简单。首先执行上述步骤，然后创建DB创建脚本(righ单击SSMS中的数据库，选择Generate Scripts -> database create)。然后在本地数据库上重新播放此脚本。

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.

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

#2

I just wanted to add a simplified version of dumbledad's answer, since it is the correct one.

我只是想添加一个简化版的邓布利多的答案，因为它是正确的。

1. Export the Azure SQL Database to a BACPAC file on blob storage.
2. 将Azure SQL数据库导出到blob存储上的BACPAC文件。
3. From inside SQL Management studio, right-click your database, click "import data-tier application".
4. 在SQL Management studio中，右键单击数据库，单击“导入数据层应用程序”。
5. You'll be prompted to enter the information to get to the BACPAC file on your Azure blob storage.
6. 您将被提示输入信息以获取Azure blob存储上的BACPAC文件。
7. Hit next a couple times and... Done!
8. 接下来几次，然后…完成了!

#3

I think it is a lot easier now.

我觉得现在容易多了。

1. Launch SQL Management Studio
2. 启动SQL管理工作室
3. Right Click on "Databases" and select "Import Data-tier application..."
4. 右键单击“数据库”并选择“导入数据层应用程序…”
5. The wizard will take you through the process of connecting to your Azure account, creating a BACPAC file and creating your database.
6. 向导将带领您完成连接到Azure帐户、创建BACPAC文件和创建数据库的过程。

Additionally, I use Sql Backup and FTP (https://sqlbackupandftp.com/) to do daily backups to a secure FTP server. I simply pull a recent BACPAC file from there and it import it in the same dialog, which is faster and easier to create a local database.

另外，我使用Sql备份和FTP (https://sqlbackupandftp.com/)来对安全的FTP服务器进行日常备份。我只是从那里提取一个最近的BACPAC文件，它将它导入到同一个对话框中，这样创建本地数据库会更快、更容易。

#4

In SQL Server 2016 Management Studio, the process for getting an azure database to your local machine has been streamlined.

在SQL Server 2016 Management Studio中，向本地机器获取azure数据库的过程已经被简化。

Right click on the database you want to import, click Tasks > Export data-tier application, and export your database to a local .dacpac file.

右键单击要导入的数据库，单击Tasks >导出数据层应用程序，并将数据库导出到本地的.dacpac文件。

In your local target SQL server instance, you can right click Databases > Import data-tier application, and once it's local, you can do things like backup and restore the database.

在本地目标SQL服务器实例中，您可以右键单击database >导入数据层应用程序，一旦它是本地的，您就可以执行备份和恢复数据库之类的操作。

#5

You can also check out SQL Azure Data Sync in the Windows Azure Management Portal. It allows you to retrieve and restore an entire database, including schema and data between SQL Azure and SQL Server.

您还可以在Windows Azure管理门户中检查SQL Azure数据同步。它允许您检索和恢复整个数据库，包括SQL Azure和SQL Server之间的模式和数据。

#6

Using msdeploy.exe

Caveat: msdeploy.exe fails to create the destination database on its own, so you need to create it manually first.

警告:msdeploy。exe无法自行创建目标数据库，因此需要首先手动创建它。

1. Copy the connection string on the database properties page. Adjust it so that it contains a correct password.
2. 复制数据库属性页上的连接字符串。调整它，使它包含一个正确的密码。
3. Get the connection string for the destination DB.
4. 获取目标DB的连接字符串。

5. Run msdeploy.exe like this:

   msdeploy运行。exe是这样的:

   "c:\Program Files\IIS\Microsoft Web Deploy V3\msdeploy.exe" -verb:sync -dest:dbDacFx="destination_DB_connection_string",dropDestinationDatabase=true -source:dbDacFx="azure_DB_connection_string",includeData=true -verbose
   

Using SqlPackage.exe

1. Export the azure DB to a bacpac package.

   将azure DB导出到bacpac包。

   "c:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Export /ssn:"azure_db_server" /sdn:"azure_db_name" /su:"user_name" /sp:"password" /tf:"file.bacpac"
   

2. Import the package to a local DB.

   将包导入到本地数据库。

   "c:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Import /SourceFile:"file.bacpac" /TargetServerName:".\SQLEXPRESS" /TargetDatabaseName:CopyOfAzureDb
   

#7

It's pretty easy. This worked for me...in terms of getting an Azure SQL database down onto your local machine...:

它很简单。这工作对我来说…要将Azure SQL数据库放到本地机器上……

1. Open your SQL Management Studio and connect to your Azure SQL Server.
2. 打开SQL Management Studio并连接到Azure SQL服务器。
3. Select the database you would like to get down onto your local machine, and right-click...select "Generate Scripts". Follow the prompts...
4. 选择您希望在本地计算机*问的数据库，然后右键单击……选择“生成脚本”。按照提示…

BUT, be careful in that if you ALSO want the DATA, as well as the scripts, be sure to check the Advanced Options before beginning the generating...scroll down to "Types of data to script", and make sure you have "Schema and data"...or whatever is appropriate for you.

但是，请注意，如果您还想要数据和脚本，请确保在开始生成之前检查高级选项……向下滚动“数据类型到脚本”，并确保您有“模式和数据”……或者任何适合你的。

It will give you a nice SQL script file which can then be run on your local machine and it will create the database as well as populate it with all the data.

它将为您提供一个漂亮的SQL脚本文件，然后可以在本地机器上运行，它将创建数据库并使用所有数据填充它。

Bare in mind that in my case, I have no FK or other constraints...also, it wasn't a lot of data.

请记住，在我的情况下，我没有FK或其他约束……而且，数据也不是很多。

I don't recommend this as a backup mechanism in general...

一般来说，我不建议把它作为一种备份机制……

#8

Please try this: http://mooneyblog.mmdbsolutions.com/index.php/2011/01/11/simple-database-backups-with-sql-azure/.

请试试这个:http://mooneyblog.mmdbsolutions.com/index.php/2011/01/11/simple-database-backups-with-sql-azure/。

Other alternative would be SQL Azure Data Sync.

另一种选择是SQL Azure数据同步。

There is one more blog post I ran across a few days back but couldn't find it now. I will update this post as soon as I find it.

几天前我又读了一篇博客，但现在找不到。我会尽快更新这篇文章。

#9

I couldn't get the SSIS import / export to work as I got the error 'Failure inserting into the read-only column "id"'. Nor could I get http://sqlazuremw.codeplex.com/ to work, and the links above to SQL Azure Data Sync didn't work for me.

由于在只读列“id”中插入了错误的“失败”，我无法使SSIS导入/导出正常工作。我也无法让http://sqlazuremw.codeplex.com/正常工作，上面的SQL Azure数据同步链接对我不起作用。

But I found an excellent blog post about BACPAC files: http://dacguy.wordpress.com/2012/01/24/sql-azure-importexport-service-has-hit-production/

但我发现了一篇关于BACPAC文件的优秀博客文章:http://dacguy.wordpress.com/2012/01/24/sql- azg -importexport- has-hit-production/

In the video in the post the blog post's author runs through six steps:

在这篇博文的视频中，作者经历了六个步骤:

1. Make or go to a storage account in the Azure Management Portal. You'll need the Blob URL and the Primary access key of the storage account.

   创建或访问Azure管理门户中的存储帐户。您将需要Blob URL和存储帐户的主访问键。

2. The blog post advises making a new container for the bacpac file and suggests using the Azure Storage Explorer for that. (N.B. you'll need the Blob URL and the Primary access key of the storage account to add it to the Azure Storage Explorer.)

   这篇博文建议为bacpac文件创建一个新的容器，并建议为此使用Azure Storage Explorer。(N.B.您需要将Blob URL和存储帐户的主访问键添加到Azure存储资源管理器中。)

3. In the Azure Management Portal select the database you want to export and click 'Export' in the Import and Export section of the ribbon.

   在Azure管理门户中，选择要导出的数据库，并单击功能区的导入和导出部分中的“export”。

4. The resulting dialogue requires your username and password for the database, the blob URL, and the access key. Don't forget to include the container in the blob URL and to include a filename (e.g. https://testazurestorage.blob.core.windows.net/dbbackups/mytable.bacpac).

   所产生的对话需要您的用户名和密码用于数据库、blob URL和访问密钥。不要忘记在blob URL中包含容器并包含文件名(例如https://testazurestorage.blob.core.net/dbbackups/mytable.bacpac)。

5. After you click Finish the database will be exported to the BACPAC file. This can take a while. You may see a zero byte file show up immediately if you check in the Azure Storage Explorer. This is the Import / Export Service checking that it has write access to the blob-store.

   单击Finish之后，数据库将被导出到BACPAC文件。这可能需要一段时间。如果检入Azure存储资源管理器，您可能会立即看到一个零字节的文件。这是对blob-store的写访问的导入/导出服务检查。

6. Once that is done you can use the Azure Storage Explorer to download the BACPAC file and then in the SQL Server Management Studio right-click your local server's database folder and choose Import Data Tier Application that will start the wizard which reads in the BACPAC file to produce the copy of your Azure database. The wizard can also connect directly to the blob-store to obtain the BACPAC file if you would rather not copy it locally first.

   一旦完成,您可以使用Azure存储浏览器下载BACPAC文件,然后在SQL Server Management Studio右键单击本地服务器的数据库文件夹并选择导入数据层应用程序将开始向导BACPAC读取的文件生产Azure数据库的副本。如果不希望首先在本地复制的话，向导还可以直接连接到blob-store来获取BACPAC文件。

The last step may only be available in the SQL Server 2012 edition of the SQL Server Management Studio (that's the version I am running). I do not have earlier ones on this machine to check. In the blog post the author uses the command line tool DacImportExportCli.exe for the import which I believe is available at http://sqldacexamples.codeplex.com/releases

最后一步可能只在SQL Server Management Studio的SQL Server 2012版本中可用(这是我正在运行的版本)。我在这台机器上没有先前的检查。在博文中，作者使用命令行工具DacImportExportCli。我认为可以在http://sqldacexamples.codeplex.com/release中找到这个导入的exe

#10

Regarding the " I couldn't get the SSIS import / export to work as I got the error 'Failure inserting into the read-only column "id"'. This can be gotten around by specifying in the mapping screen that you do want to allow Identity elements to be inserted.

关于“我无法让SSIS导入/导出工作，因为我得到了将错误‘失败’插入到只读列“id”中。可以通过在映射屏幕中指定允许插入标识元素来实现这一点。

After that, everything worked fine using SQL Import/Export wizard to copy from Azure to local database.

之后，使用SQL Import/Export向导将Azure复制到本地数据库，一切都运行良好。

I only had SQL Import/Export Wizard that comes with SQL Server 2008 R2 (worked fine), and Visual Studio 2012 Express to create local database.

我只使用SQL Server 2008 R2(工作良好)和Visual Studio 2012 Express来创建本地数据库的SQL导入/导出向导。

#11

The accepted answer is out of date. I found a better answer: Use Import Data-tier Application

被接受的答案已经过时了。我找到了一个更好的答案:使用导入数据层应用程序

More detailed information please see this article: Restoring Azure SQL Database to a Local Server

更详细的信息请参见本文:将Azure SQL数据库还原到本地服务器

#12

You can try with the tool "SQL Database Migration Wizard". This tool provide option to import and export data from azure sql.

您可以尝试使用“SQL数据库迁移向导”工具。此工具提供了从azure sql导入和导出数据的选项。

Please check more details here.

请在这里查看更多细节。

https://sqlazuremw.codeplex.com/

https://sqlazuremw.codeplex.com/

#13

Copy Azure database data to local database: Now you can use the SQL Server Management Studio to do this as below:

将Azure数据库数据复制到本地数据库:现在可以使用SQL Server Management Studio进行如下操作:

• Connect to the SQL Azure database.
• 连接到SQL Azure数据库。
• Right click the database in Object Explorer.
• 右键单击Object Explorer中的数据库。
• Choose the option "Tasks" / "Deploy Database to SQL Azure".
• 选择“Tasks”/“将数据库部署到SQL Azure”选项。
• In the step named "Deployment Settings", connect local SQL Server and create New database.
• 在名为“部署设置”的步骤中，连接本地SQL Server并创建新的数据库。

"Next" / "Next" / "Finish"

"Next" / "Next" / "Finish"

#14

Download Optillect SQL Azure Backup - it has 15-day trial, so it will be enough to move your database :)

下载Optillect SQL Azure Backup——它有15天的试用期，因此足以移动数据库:)

#15

You can use the new Azure Mobile Services to do a nightly backup export from SQL Azure to a .bacpac file hosted in Azure Storage. This solution is 100% cloud, doesn't require a 3rd party tool and doesn't require a local hosted SQL Server instance to download/copy/backup anything.

您可以使用新的Azure移动服务来执行从SQL Azure到Azure存储中的.bacpac文件的夜间备份导出。这个解决方案是100%的云，不需要第三方工具，也不需要本地托管的SQL Server实例来下载/复制/备份任何东西。

There's about 8 different steps, but they're all easy: http://geekswithblogs.net/BenBarreth/archive/2013/04/15/how-to-create-a-nightly-backup-of-your-sql-azure.aspx

有8个不同的步骤，但是它们都很简单:http://geekswithblogs.net/benbarreth/archive/2013/04/15 /how- how-to-create-a-night -backup- sql-azure.aspx

#16

The trick for me was to start replicating PKs/FKs/constraints on empty DB, then temporarily disable constraints while importing data (see https://*.com/a/161410).

我的诀窍是在空DB上开始复制PKs/FKs/约束，然后在导入数据时暂时禁用约束(参见https://*.com/a/161410)。

More precisely:

更准确地说应该是:

1. Create empty target DB manually;
2. 手动创建空目标DB;
3. Right-click source DB > Tasks > Generate Scripts;
4. 右击源DB >任务>生成脚本;
5. Run script file on empty target DB (now DB has correct PKs/FKs/constraints, but no data);
6. 在空目标DB上运行脚本文件(现在DB有正确的PKs/FKs/约束，但没有数据);
7. Disable all constraints;
8. 禁用所有约束;
9. Import data (Right-click target DB > Tasks > Import Data);
10. 导入数据(右键单击目标DB >任务>导入数据);
11. Re-enable constraints.
12. 重新启用约束。

Hope this helps!

希望这可以帮助!

#17

Now you can use the SQL Server Management Studio to do this.

现在您可以使用SQL Server Management Studio来完成这一任务。

• Connect to the SQL Azure database.
• 连接到SQL Azure数据库。
• Right click the database in Object Explorer.
• 右键单击Object Explorer中的数据库。
• Choose the option "Tasks" / "Deploy Database to SQL Azure".
• 选择“Tasks”/“将数据库部署到SQL Azure”选项。
• In the step named "Deployment Settings", select your local database connection.
• 在名为“部署设置”的步骤中，选择本地数据库连接。
• "Next" / "Next" / "Finish"...
• “Next”/“Next”/“Finish”……

#18

Use the Import/Export service in SQL Azure to create a .bacpac file.

使用SQL Azure中的导入/导出服务创建.bacpac文件。

Then take a look at this method in another Stack Overflow article.

然后在另一篇Stack Overflow文章中查看这个方法。

Azure SQL Database Bacpac Local Restore

Azure SQL数据库Bacpac本地恢复

#19

If anyone has a problem to import a Bacpac of a DB that uses Azure SQL Sync, Sandrino Di Mattia developed a great simple application to solve this.

如果有人有问题需要导入使用Azure SQL Sync的DB的Bacpac, Sandrino Di Mattia开发了一个非常简单的应用程序来解决这个问题。

1. Export a Bacpac of your DB
2. 导出您的DB的Bacpac
3. Dowload Di Mattia's binary
4. Dowload Di Mattia二进制
5. With this console app repair the downloaded Bacpac
6. 使用这个控制台应用程序修复下载的Bacpac
7. Lauch SSMS
8. 开展地对地导弹
9. Right Click on "Databases" and select "Import Data-tier Application"
10. 右键单击“数据库”并选择“导入数据层应用程序”
11. Select the repaired Bacpac.
12. 选择修复Bacpac。

#20

If anyone wants a free and effective option (and don't mind doing it manually) to backup database to Local then use schema and data comparison functionality built into the latest version Microsoft Visual Studio 2015 Community Edition (Free) or Professional / Premium / Ultimate edition. It works like a charm!

如果有人想要一个免费和有效的选项(不介意手工操作)来备份数据库到本地，那么使用最新版本的Microsoft Visual Studio 2015 Community Edition(免费)或专业/高级/终极版中的模式和数据比较功能。它就像一种魔力!

I have BizPark account with Azure and there is no way to backup database directly without paying. I found this option in VS works.

我有Azure的BizPark帐户，没有办法直接备份数据库。我在VS中找到了这个选项。

Answer is taken from https://*.com/a/685073/6796187

答案选自https://*.com/a/685073/6796187

#21

Hi I'm using the SQLAzureMW tool for SQLAzure DB migration and management. Very useful one. It was downloaded from codeplex, but currently it's not available the codeplex will be going to shutdown, the same application tool is now available in GttHub. This below link is explain how to use this tool and also available the Application for download.

大家好，我正在使用SQLAzureMW工具进行SQLAzure DB迁移和管理。非常有用的一个。它是从codeplex上下载的，但是目前还没有，codeplex将会被关闭，GttHub上也有同样的应用工具。下面的链接将解释如何使用这个工具，以及如何下载这个应用程序。

https://github.com/twright-msft/azure-content/blob/master/articles/sql-database/sql-database-migration-wizard.md

https://github.com/twright-msft/azure-content/blob/master/articles/sql-database/sql-database-migration-wizard.md

#22

I always use Import/Export Feature which seems to be the easiest one among all.

我总是使用导入/导出功能，这似乎是所有功能中最简单的一个。

Step 1:

步骤1:

Get the backup from the azure instance as follows, Select the database → Right click → Tasks → Export Data Tier Application.

从azure获取备份实例如下,选择数据库→右键→→出口数据层应用程序的任务。

Step 2: Give a specific name for the backup file and save it in your desired location

步骤2:为备份文件指定一个特定的名称，并将其保存在所需的位置

Step 3: That's it you have taken a backup of the database from sql instance to your local. Lets restore it to the local. Copy the backed up database to your C drive. Now open the PowerShell with administrator rights and navigate to C drive

步骤3:您已经将数据库从sql实例备份到本地。让我们把它恢复到本地。将备份的数据库复制到C驱动器。现在打开具有管理员权限的PowerShell并导航到C驱动器

Step 4: Lets download the powershell script to remove the master keyRemoveMasterKey.ps1 have the script on the same drive in this case its C.

步骤4:让我们下载powershell脚本，以删除主keyRemoveMasterKey。在这种情况下，ps1在同一个驱动器上有脚本C。

Step 5 : Run the script as follows, .\RemoveMasterKey.ps1 -bacpacPath "C:\identity.bacpac"

步骤5:运行脚本如下，\RemoveMasterKey。ps1 -bacpacPath“C:\ identity.bacpac”

That's it, now you can restore it on MSSQL 2017 in your local environment.

就是这样，现在您可以在本地环境下的MSSQL 2017上恢复它。

Step 6: Connect to your local server, and click Databases → Import-Data-Tier-Application

第六步:连接到本地服务器,然后单击数据库→Import-Data-Tier-Application

Step 7 : Give a name for your database to restore.

步骤7:为要恢复的数据库指定一个名称。

Now you will see everything in green!

现在你会看到绿色的一切!

Read my blog with diagrams.

用图表阅读我的博客。