如何将远程 SQL Server 数据库备份到本地驱动器？

How can I backup a remote SQL Server database to a local drive?

提问by Fernando

I need to copy a database from a remote server to a local one. I tried to use SQL Server Management Studio, but it only backs up to a drive on the remote server.

我需要将数据库从远程服务器复制到本地服务器。我尝试使用 SQL Server Management Studio，但它只备份到远程服务器上的驱动器。

Some points:

几点：

• I do not have access to the remote server in a way that I could copy files;
• I do not have access to setup a UNC path to my server;

• 我无法以复制文件的方式访问远程服务器；
• 我无权设置到我的服务器的 UNC 路径；

Any ideas of how can I copy this database? Will I have to use 3rd party tools?

关于如何复制此数据库的任何想法？我必须使用第 3 方工具吗？

采纳答案by Daniel Gill

In Microsoft SQL Server Management Studio you can right-click on the database you wish to backup and click Tasks -> Generate Scripts.

在 Microsoft SQL Server Management Studio 中，您可以右键单击要备份的数据库，然后单击任务 -> 生成脚本。

This pops open a wizard where you can set the following in order to perform a decent backup of your database, even on a remote server:

这会弹出一个向导，您可以在其中设置以下内容，以便对您的数据库进行适当的备份，即使在远程服务器上：

• Select the database you wish to backup and hit next,
• In the options it presents to you:
  1. In 2010: under the Table/View Options, change 'Script Data' and 'Script Indexes' to True and hit next,
  2. In 2012: under 'General', change 'Types of data to script' from 'Schema only' to 'Schema and data'
  3. In 2014: the option to script the data is now "hidden" in step "Set Scripting Options", you have to click the "Advanced" and set "Types of data to script" to "Schema and data" value
• In the next fourwindows, hit 'select all' and then next,
• Choose to script to a new query window

• 选择您要备份的数据库并点击下一步，
• 在它提供给您的选项中：
  1. 2010 年：在表/视图选项下，将“脚本数据”和“脚本索引”更改为 True 并点击下一步，
  2. 2012 年：在“常规”下，将“数据类型到脚本”从“仅限架构”更改为“架构和数据”
  3. 在 2014 年：脚本数据的选项现在在“设置脚本选项”步骤中“隐藏”，您必须单击“高级”并将“数据类型到脚本”设置为“架构和数据”值
• 在接下来的四个窗口中，点击“全选”，然后下一步，
• 选择脚本到新的查询窗口

Once it's done its thing, you'll have a backup script ready in front of you. Create a new local (or remote) database, and change the first 'USE' statement in the script to use your new database. Save the script in a safe place, and go ahead and run it against your new empty database. This should create you a (nearly) duplicate local database you can then backup as you like.

一旦它完成它的事情，你就会有一个准备好的备份脚本在你面前。创建一个新的本地（或远程）数据库，并更改脚本中的第一个“USE”语句以使用您的新数据库。将脚本保存在安全的地方，然后针对新的空数据库运行它。这应该会为您创建一个（几乎）重复的本地数据库，然后您可以根据需要进行备份。

If you have fullaccess to the remote database, you can choose to check 'script all objects' in the wizard's first window and then change the 'Script Database' option to True on the next window. Watch out though, you'll need to perform a full search & replace of the database name in the script to a new database which in this case you won't have to create before running the script. This should create a more accurate duplicate but is sometimes not available due to permissions restrictions.

如果您拥有远程数据库的完全访问权限，您可以选择在向导的第一个窗口中选中“脚本所有对象”，然后在下一个窗口中将“脚本数据库”选项更改为 True。但请注意，您需要执行完整搜索并将脚本中的数据库名称替换为新数据库，在这种情况下，您无需在运行脚本之前创建该数据库。这应该会创建一个更准确的副本，但有时由于权限限制而无法使用。

回答by Shaun Luttin

To copy data and schema only (will not copy stored procedures, functions etc.), use the SQL Server Import and Export Wizard, and choose New...when choosing the destination database.

要仅复制数据和架构（不会复制存储过程、函数等），请使用 SQL Server 导入和导出向导，并在选择目标数据库时选择新建...。

Right Click Database > Tasks > Import Data.

右键单击数据库 > 任务 > 导入数据。

Choose a Data Source

选择数据源

• Data Source: SQL Server Native Client
• Server Name: the remote server
• Authentication:
• Database: the db name

• 数据源：SQL Server Native Client
• 服务器名称：远程服务器
• 认证：
• 数据库：数据库名称

Choose a Destination

选择目的地

• Data Source: SQL Server Native Client
• Server Name: the local server
• Authentication:
• Database: New...

• 数据源：SQL Server Native Client
• 服务器名称：本地服务器
• 认证：
• 数据库：New...

The rest is straight forward.

剩下的就是直截了当。

回答by T. Webster

First, grant full control permissions to a local path on your machine (as shown below) with Everyone. (Or alternatively grant permissions specificallyto the SQL Server Agent account).

首先，使用Everyone 授予对您机器上本地路径的完全控制权限（如下所示）。（或者，也可以专门向 SQL Server 代理帐户授予权限）。

Second, execute the following:

其次，执行以下操作：

BACKUP DATABASE [dev] TO  DISK = N'\myMachine\c\dev.bak' WITH COPY_ONLY, INIT;

回答by marc_s

You cannotcreate a backup from a remote server to a local disk - there is just no way to do this. And there are no third-party tools to do this either, as far as I know.

您无法创建从远程服务器到本地磁盘的备份 - 没有办法做到这一点。据我所知，也没有第三方工具可以做到这一点。

All you can do is create a backup on the remote server machine, and have someone zip it up and send it to you.

您所能做的就是在远程服务器计算机上创建一个备份，然后让某人将其压缩并发送给您。

回答by Phill C

I know this is late answer but I have to make a comment about most voted answer that says to use generate scripts option in SSMS.

我知道这是迟到的答案，但我必须对大多数投票的答案发表评论，该答案说在 SSMS 中使用生成脚本选项。

Problem with that is this option doesn't necessarily generate script in correct execution order because it doesn't take dependencies into account.

问题在于此选项不一定以正确的执行顺序生成脚本，因为它没有考虑依赖关系。

For small databases this is not an issue but for larger ones it certainly is because it requires to manually re-order that script. Try that on 500 object database ;)

对于小型数据库，这不是问题，但对于较大的数据库，这肯定是因为它需要手动重新排序该脚本。在 500 个对象数据库上试试；)

Unfortunately in this case the only solution are third party tools.

不幸的是，在这种情况下，唯一的解决方案是第三方工具。

I successfully used comparison tools from ApexSQL(Diff and Data Diff) for similar tasks but you can't go wrong with any other already mentioned here, especially Red Gate.

我成功地将ApexSQL 的比较工具（Diff 和 Data Diff）用于类似的任务，但这里已经提到的任何其他工具都不会出错，尤其是 Red Gate。

回答by adinas

You can try SQLBackupAndFTP. It will create scripts to create all the objects in your database and INSERT statements for all the rows in your tables. In any database you can run this script file and the entire database will be re-created.

您可以尝试SQLBackupAndFTP。它将创建脚本以创建数据库中的所有对象，并为表中的所有行创建 INSERT 语句。在任何数据库中，您都可以运行此脚本文件，然后将重新创建整个数据库。

回答by Marcel W

Look at this blog for a description how to copy a remote database:

查看此博客以了解如何复制远程数据库：

Backup a SQL Server 2008 Database From a Shared Hosting Environment

从共享主机环境备份 SQL Server 2008 数据库

回答by okarpov

There is the 99% solution to get bak file from remote sql server to your local pc. I described it there in my post http://www.ok.unsode.com/post/2015/06/27/remote-sql-backup-to-local-pc

有 99% 的解决方案可以将 bak 文件从远程 sql server 获取到本地电脑。我在我的帖子http://www.ok.unsode.com/post/2015/06/27/remote-sql-backup-to-local-pc 中描述了它

In general it will look like this:

一般来说，它看起来像这样：

• execute sql script to generate bak files

• execute sql script to insert each bak file into temp table with varbinary field type and select this row and download data

• repeat prev. step as many time as you have bak files

• execute sql script to remove all temporary resources

• 执行sql脚本生成bak文件

• 执行 sql 脚本将每个 bak 文件插入到具有 varbinary 字段类型的临时表中并选择该行并下载数据

• 重复上一个 步骤与 bak 文件一样多

• 执行sql脚本删除所有临时资源

that's it, you have your bak files on your local pc.

就是这样，你的本地电脑上有你的 bak 文件。

回答by Pouyan

You can use Copy database ... right click on the remote database ... select tasks and use copy database ... it will asks you about source server and destination server . that your source is the remote and destination is your local instance of sql server.

您可以使用复制数据库...右键单击远程数据库...选择任务并使用复制数据库...它会询问您有关源服务器和目标服务器的信息。你的源是远程的，目标是你本地的 sql server 实例。

it's that easy

就这么简单

回答by Daniel Gill

The AppHarbor gang has been struggling with this and has developed a temporary solution using SQL server management objects and SqlBulkCopy.

AppHarbor 团伙一直在为此苦苦挣扎，并开发了一个使用 SQL 服务器管理对象和 SqlBulkCopy 的临时解决方案。

Check out their blog postabout it, or go straight to the code.

查看他们关于它的博客文章，或直接查看代码。

They've only tested it with AppHarbor but it may be worth checking out.

他们只使用 AppHarbor 对其进行了测试，但它可能值得一试。