如何将远程SQL服务器数据库备份到本地驱动器?

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?

有什么办法复制这个数据库吗?我需要使用第三方工具吗?

20 个解决方案

#1

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中，您可以右键单击希望备份的数据库并单击Tasks ->生成脚本。

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,
• 选择要备份的数据库并单击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. 2010年:在表/视图选项下，将“脚本数据”和“脚本索引”更改为True并点击next，
  3. In 2012: under 'General', change 'Types of data to script' from 'Schema only' to 'Schema and data'
  4. 2012年:在“General”下，将“数据类型”改为“script”，从“Schema only”改为“Schema and data”
  5. 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
  6. 2014年:数据脚本的选项现在“隐藏”在步骤“设置脚本选项”中，您必须单击“高级”并将“数据类型”设置为“模式和数据”值
• 在选择礼物给你:2010年:根据表/视图选项,改变“脚本数据”和“脚本索引”为真,点击下一步,在2012年,在“一般”,改变“类型的数据脚本”从“模式只有”2014年的模式和数据:选择脚本的数据现在是一步“隐藏”“设置脚本选项”,你必须点击“高级”和“脚本”的数据类型设置为“模式和数据”的价值
• In the next four windows, hit 'select all' and then next,
• 在接下来的四个窗口中，点击“select all”，然后是next，
• Choose to script to a new query window
• 选择为一个新的查询窗口编写脚本

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 full access 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.

如果您完全访问远程数据库，您可以选择在向导的第一个窗口中检查“脚本所有对象”，然后在下一个窗口中更改“脚本数据库”选项。但是要注意，您需要对脚本中的数据库名进行完整的搜索和替换，以找到一个新的数据库，在这种情况下，您在运行脚本之前不需要创建这个数据库。这应该会创建一个更精确的副本，但由于权限限制，有时不可用。

#2

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导入和导出向导，选择New…选择目标数据库时。

Right Click Database > Tasks > Import Data.

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

Choose a Data Source

选择一个数据源

• Data Source: SQL Server Native Client
• 数据源:SQL Server原生客户端。
• Server Name: the remote server
• 服务器名:远程服务器
• Authentication:
• 身份验证:
• Database: the db name
• 数据库:数据库名称

Choose a Destination

选择一个目的地

• Data Source: SQL Server Native Client
• 数据源:SQL Server原生客户端。
• Server Name: the local server
• 服务器名:本地服务器
• Authentication:
• 身份验证:
• Database: New...
• 数据库:新…

The rest is straight forward.

其余的直接向前。

#3

You cannot create 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.

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

#4

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

首先，将完全控制权限授予您的机器上的本地路径(如下所示)。(或者专门授予SQL Server代理帐户的权限)。

Second, execute the following:

第二,执行以下:

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

#5

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，您都不会出错。

#6

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。它将创建脚本来创建数据库中的所有对象，并为表中的所有行插入语句。在任何数据库中，都可以运行这个脚本文件，并重新创建整个数据库。

#7

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数据库

#8

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

它是那么容易

#9

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服务器获取到本地pc。我在我的文章http://www.ok.unsode.com/post/2015/06/27/remote-sql-back -to-local-pc中描述了这一点

In general it will look like this:

一般来说，它是这样的:

• execute sql script to generate bak files

  执行sql脚本以生成bak文件

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

  执行sql脚本，将每个bak文件插入到带有varbinary字段类型的temp表中，并选择这一行并下载数据。

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

  重复上一页。尽可能多的时间，因为你有bak文件。

• execute sql script to remove all temporary resources

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

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

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

#10

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

AppHarbor帮一直在努力解决这个问题，并使用SQL server管理对象和SqlBulkCopy开发了一个临时解决方案。

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

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

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

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

#11

The answers above are just not correct. A SQL Script even with data is not a backup. A backup is a BAK file that contains the full database in its current structure including indizes.

上面的答案就是不正确。即使有数据的SQL脚本也不是备份。备份是一个BAK文件，它包含当前结构中的完整数据库，包括indizes。

Of course a BAK file containg the full backup with all data and indizes from a remote SQL Server database can be retrieved on a local system.

当然，可以在本地系统上检索与所有数据和远程SQL Server数据库相连接的BAK文件。

This can be done with commercial software, to directly save a backup BAK file to your local machine, for example This one will directly create a backup from a remote SQL db on your local machine.

这可以通过商业软件实现，直接将备份BAK文件保存到本地机器，例如，这个文件将直接从本地机器上的远程SQL db创建备份。

#12

As Martin Smith said, if you have no access to the machine or the filesystem, you will need to use third party tools, like Red Gate or Adept to do a compare on the source and destination systems. Red Gate's tools will allow you to copy the objects and schemas AND the data.

正如Martin Smith所说，如果您无法访问机器或文件系统，那么您将需要使用第三方工具，如Red Gate或Adept，来对源系统和目标系统进行比较。Red Gate的工具将允许您复制对象、模式和数据。

#13

just try this one:

试试这个:

1)Share a folder with full permission in your computer

1)在您的计算机*享一个具有完全权限的文件夹

2) in your SQL server : control panel -> administrative tools -> services -> right click on all SQL services

2)在您的SQL服务器:控制面板—>管理工具—>服务—>右键单击所有SQL服务

on log on tab should start with your domain administrator

on log on选项卡应该从域管理员开始

3) in maintenance wizard of sql server place the back up location and folder (\yourcomputername\sharedfoldernam)

3)在sql server的维护向导中，放置备份位置和文件夹(\您的计算机名\sharedfoldernam)

I did remote backup on 8 server of sql server 2008 in our company

我在我公司做了sql server 2008的8台服务器的远程备份

#14

I'm astonished that no-one has mentioned the scripted backup solution offered by Ola Hallengren which absolutely does allow you to backup a DB from a remote server to a UNC path on your network for free (I'm actually using it as I type to backup a DB from a dev server to which I have no remote access other than through SSMS to a share on my dev PC). This has been available since 2008 and works on SQL Server 2005 through to 2014.

我惊讶地发现没人提到提供的脚本备份解决方案Ola Hallengren哪个绝对让你备份数据库从一个远程服务器UNC路径网络上免费(我用它作为我从开发服务器备份一个DB型以外,我没有远程访问通过每股地对地导弹运到我开发PC)。这从2008年开始就可以使用了，可以在SQL Server 2005到2014年间使用。

You need to ensure that the share you set up has enough access: I tend to allow full read/write to the 'Everyone' AD group for the duration of the backup process because I'm too lazy to figure out anything more restrictive but that's personal choice.

您需要确保您设置的共享具有足够的访问权限:在备份过程中，我倾向于允许“所有人”广告组进行完整的读/写操作，因为我太懒了，不知道有什么限制，但这是个人选择。

It's well-used, well-documented and very flexible. I tend to put the procs and the logging table in their own little utility database and then fire it up. Provided everything is in your AD domain and not remote in the sense that it's out on a co-located server or something, this works very well.

它使用得很好，有很好的文档说明，并且非常灵活。我倾向于将procs和日志表放在它们自己的小实用程序数据库中，然后启动它。如果所有的东西都在你的广告域内，而不是在一个共同定位的服务器或其他什么东西上的远程，这将非常有效。

Apologies for adding to a very old thread but I came across this when looking for something else and figured it was a worthwhile addition for anyone looking for this topic.

为添加到一个非常旧的线程而道歉，但我在寻找其他东西时遇到了这个问题，并认为这对任何寻找这个主题的人来说都是值得的。

#15

I could do that once...TO do this you have to have a share opened on the remote server. then you can directly place the backup on the share itself, than the default location...

我可以做一次…要做到这一点，您必须在远程服务器上打开一个共享。然后，您可以直接将备份放在共享本身上，而不是默认位置…

Usually the admin takes the backup and shares it with us in some shared folder. I tried if that will work if i place the backup there. It worked.

通常管理员接受备份并在共享文件夹中与我们共享它。我试过，如果我把备份放在那里，它是否有效。它工作。

#16

yone way you could take a backup from a remote SQL Server instance to your local drive, given the following condition is met:

yone方法可以将远程SQL Server实例备份到本地驱动器，前提是满足以下条件:

1. You have a shared folder on your local drive.
2. 本地驱动器上有一个共享文件夹。
3. the shared folder is accessible from the SQL Server box.
4. 可以从SQL服务器框中访问共享文件夹。

Now when specifying the backup command, use the shared folder path when specifying the disk option.

现在，在指定备份命令时，在指定磁盘选项时使用共享文件夹路径。

#17

If you use the Generate Scripts under SSMS, click the Advanced button. Under the 'Generate Scripts for the dependent objects' option, click True. By clicking that any dependencies of each object will also be scripted out in proper order.

如果使用SSMS下的生成脚本，请单击Advanced按钮。在“为依赖对象生成脚本”选项下，单击“正确”。通过单击，每个对象的任何依赖项也将按照适当的顺序进行脚本编写。

#18

Some third-party backup programs allow setting file transferring with specific network permissions. It it very useful when SQL Server service is running under restricted account and does not have enough network permissions. Try using EMS SQL Backup which solves this task.

一些第三方备份程序允许设置具有特定网络权限的文件传输。当SQL Server服务在受限制的帐户下运行并且没有足够的网络权限时，它非常有用。尝试使用EMS SQL Backup解决此任务。

#19

I use Redgate backup pro 7 tools for this purpose. you can create mirror from backup file in create tile on other location. and can copy backup file after create on network and on host storage automatically.

为此，我使用Redgate备份pro 7工具。您可以在其他位置的create tile中创建来自备份文件的镜像。并且可以在网络上和主机存储上自动复制备份文件。

#20

If you are on a local network you can share a folder on your local machine and use it as destination folder for the backup.

如果您在本地网络上，您可以在本地机器上共享一个文件夹，并将其用作备份的目标文件夹。

Example:

例子:

• Local folder:

  本地文件夹:

  C:\MySharedFolder -> URL: \\MyMachine\MySharedFolder

  C:\ MySharedFolder - > URL:\ \ MyMachine \ MySharedFolder

• Remote SQL Server:

  远程SQL服务器:

  Select your database -> Tasks -> Back Up -> Destination -> Add -> Apply '\\MyMachine\MySharedFolder'

  选择你的数据库->任务->备份->目标->添加->应用'\\MyMachine\MySharedFolder'