如何将远程 SQL Server 数据库备份到本地驱动器?
我需要将数据库从远程服务器复制到本地服务器。我尝试使用 SQL Server Management Studio,但它仅备份到远程服务器上的驱动器。
几点:
- 我无法以复制文件的方式访问远程服务器;
- 我无权设置我的服务器的 UNC 路径;
关于如何复制这个数据库有什么想法吗?我必须使用第三方工具吗?
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.
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;
Any ideas of how can I copy this database? Will I have to use 3rd party tools?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(25)
在 Microsoft SQL Server Management Studio 中,您可以右键单击要备份的数据库,然后单击“任务”->“备份”。生成脚本。
这会弹出一个向导,您可以在其中设置以下内容,以便即使在远程服务器上也能对数据库进行适当的备份:
一旦完成它的工作,您将在前面准备好一个备份脚本你。创建一个新的本地(或远程)数据库,并更改脚本中的第一个“USE”语句以使用新数据库。将脚本保存在安全的地方,然后继续针对新的空数据库运行它。这应该会为您创建一个(几乎)重复的本地数据库,然后您可以根据需要进行备份。
如果您对远程数据库具有完全访问权限,则可以选择在向导的第一个窗口中选中“为所有对象编写脚本”,然后在下一个窗口中将“脚本数据库”选项更改为 True。但请注意,您需要执行完整的搜索和搜索。将脚本中的数据库名称替换为新数据库,在这种情况下,您不必在运行脚本之前创建该数据库。这应该会创建更准确的副本,但有时由于权限限制而无法使用。
In Microsoft SQL Server Management Studio you can right-click on the database you wish to backup and click Tasks -> Generate Scripts.
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:
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.
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.
首先,向Everyone 授予对计算机上本地路径(如下所示)的完全控制权限。 (或者专门授予权限到 SQL Server 代理帐户)。
其次,执行以下命令:
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).
Second, execute the following:
要仅复制数据和架构(不会复制存储过程、函数等),请使用 SQL Server 导入和导出向导,并在选择目标数据库时选择新建...。
右键单击数据库>任务>导入数据。
选择数据源
选择目标
新建...
其余的很简单。
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.
Right Click Database > Tasks > Import Data.
Choose a Data Source
Choose a Destination
New...
The rest is straight forward.
您无法创建从远程服务器到本地磁盘的备份 - 没有办法做到这一点。据我所知,也没有第三方工具可以做到这一点。
您所能做的就是在远程服务器计算机上创建备份,然后让某人将其压缩并发送给您。
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.
我知道这是迟到的答案,但我必须对投票最多的答案发表评论,该答案表示在 SSMS 中使用生成脚本选项。
问题是此选项不一定以正确的执行顺序生成脚本,因为它没有考虑依赖性。
对于小型数据库来说,这不是问题,但对于较大的数据库来说,这肯定是问题,因为它需要手动重新排序该脚本。在 500 个对象数据库上尝试一下;)
不幸的是,在这种情况下,唯一的解决方案是第三方工具。
我成功地使用了 ApexSQL 中的比较工具(Diff 和 Data Diff)来完成类似的任务,但使用任何其他工具都不会出错这里已经提到过,特别是红门。
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.
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 ;)
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.
您可以尝试SQLBackupAndFTP。它将创建脚本来创建数据库中的所有对象,并为表中的所有行创建 INSERT 语句。在任何数据库中,您都可以运行此脚本文件,整个数据库将被重新创建。
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.
有 99% 的解决方案可以将 bak 文件从远程 sql 服务器获取到本地电脑。我在我的帖子中描述了它 https://web.archive.org/web/20231208061759/https://www.ok.unsode.com/post/2015/06/27/remote -sql-backup-to-local-pc
一般情况下会是这样的:
执行sql脚本生成bak文件
执行sql脚本将每个bak文件插入到varbinary字段类型的临时表中并选择该行并下载数据
重复前面的操作。步数与 bak 文件的数量一样多
执行 sql 脚本以删除所有临时资源
就是这样,你的bak文件在你的本地电脑上。
There is the 99% solution to get bak file from remote sql server to your local pc. I described it there in my post https://web.archive.org/web/20231208061759/https://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
that's it, you have your bak files on your local pc.
我知道这是一篇较旧的文章,但就其价值而言,我发现“最简单”的解决方案是右键单击数据库,然后选择“任务”->“任务”。 “导出数据层应用程序”。此选项可能仅可用,因为服务器托管在 Azure 上(根据我过去使用 Azure 的记忆,.bacpac 格式在那里相当常见)。
完成后,您可以右键单击本地服务器“数据库”列表,然后使用“导入数据层应用程序”将数据通过 .bacpac 文件获取到本地计算机。
请记住,导出可能需要很长时间。我的导出大约花了两个小时。不过,导入部分要快得多。
I know this is an older post, but for what it's worth, I've found that the "simplest" solution is to just right-click on the database, and select "Tasks" -> "Export Data-tier Application". It's possible that this option is only available because the server is hosted on Azure (from what I remember working with Azure in the past, the .bacpac format was quite common there).
Once that's done, you can right-click on your local server "Databases" list, and use the "Import Data-tier Application" to get the data to your local machine using the .bacpac file.
Just bear in mind the export could take a long time. Took roughly two hours for mine to finish exporting. Import part is much faster, though.
查看此博客,了解如何复制远程数据库的说明:
从共享托管环境备份 SQL Server 2008 数据库
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 脚本也不是备份。备份是一个 BAK 文件,其中包含当前结构的完整数据库(包括索引)。
当然,可以在本地系统上检索包含来自远程 SQL Server 数据库的所有数据和索引的完整备份的 BAK 文件。
这可以使用商业软件来完成,直接保存备份 BAK 文件到您的本地计算机,例如这个将直接创建一个从本地计算机上的远程 SQL 数据库进行备份。
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.
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.
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.
您可以使用复制数据库...右键单击远程数据库...选择任务并使用复制数据库...它会询问您有关源服务器和目标服务器的信息。您的源是远程的,目标是您本地的 sql server 实例。
就这么简单
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.
it's that easy
如果您位于本地网络中,则可以共享本地计算机上的文件夹并将其用作备份的目标文件夹。
示例:
本地文件夹:
<代码>C:\MySharedFolder -> URL:\\MyMachine\MySharedFolder
远程 SQL Server:
选择您的数据库 ->任务->备份->目的地->添加->应用“\\MyMachine\MySharedFolder\BACKUP_NAME.bak”
If you are in 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
Remote SQL Server:
Select your database -> Tasks -> Back Up -> Destination -> Add -> Apply '\\MyMachine\MySharedFolder\BACKUP_NAME.bak'
AppHarbor 团队一直在努力解决这个问题,并使用 SQL Server 管理对象和 SqlBulkCopy 开发了一个临时解决方案。
查看他们的博文关于它,或者直接查看代码。
他们仅使用 AppHarbor 对其进行了测试,但可能值得一试。
The AppHarbor gang has been struggling with this and has developed a temporary solution using SQL server management objects and 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.
对于 2019 年,如果您想要实际的本地备份,我建议使用 mssql-scripter。是的,它是脚本,但您可以调整它以包含您想要的任何内容,其中可以包含所有数据。我编写了一个 bash 脚本来在 Linux 机器上使用它进行自动日常备份。查看我的要点:
For 2019, I would recommend using mssql-scripter if you want an actual local backup. Yes it's scripts but you can adjust it to include whatever you want, which can include all of the data. I wrote a bash script to do automated daily backups using this on a linux machine. Checkout my gist:
创建一个本地共享文件夹,具有“everyone”读/写权限
连接到目标数据库,启动备份并指向共享,如下所示
\mymachine\shared_folder\mybackup.bak
(在Windows域环境中尝试过)
Create a local shared folder, with "everyone" read/write privileges
Connect to the target database, start the backup and point to the share like below
\mymachine\shared_folder\mybackup.bak
(Tried on Windows domain environment)
正如 Martin Smith 所说,如果您无法访问计算机或文件系统,则需要使用第三方工具(例如 Red Gate 或 Adept)对源系统和目标系统进行比较。 Red Gate 的工具将允许您复制对象和模式以及数据。
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.
如果满足以下条件,您可以通过任何方式从远程 SQL Server 实例备份到本地驱动器:
现在,在指定备份命令时,请在指定磁盘选项时使用共享文件夹路径。
yone way you could take a backup from a remote SQL Server instance to your local drive, given the following condition is met:
Now when specifying the backup command, use the shared folder path when specifying the disk option.
我很惊讶没有人提到 Ola Hallengren 这绝对确实允许您免费将数据库从远程服务器备份到网络上的UNC路径(实际上,我在输入时使用它来将数据库从开发服务器备份到除了通过 SSMS 访问我的开发 PC 上的共享之外,我无法进行远程访问。该功能自 2008 年起可用,适用于 SQL Server 2005 到 2014 年。
您需要确保您设置的共享具有足够的访问权限:我倾向于允许在持续时间内对“Everyone”AD 组进行完全读/写。备份过程,因为我懒得找出任何更具限制性的东西,但这是个人选择。
它使用良好、文档齐全且非常灵活。我倾向于将过程和日志记录表放在自己的小实用程序数据库中,然后启动它。假设所有内容都在您的 AD 域中,而不是远程(即位于同一位置的服务器或其他服务器上),那么这种方法就非常有效。
很抱歉添加到一个非常旧的线程,但我在寻找其他内容时遇到了这个,并认为对于任何寻找这个主题的人来说这是一个值得添加的内容。
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.
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.
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.
在 SQL Server 2019 中使用存储过程
Using a stored procedure in SQL Server 2019
也许这在 13 年前还不是问题,但今天,通过 SSDT,您可以使用 Sqlpackage Export 功能导出 200GB 以下的 SQL Server 数据库:
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/ sqlpackage-export?view=sql-server-ver16
然后,您可以使用 SqlPackage Import 功能导入 bacpac
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import ?view=sql-server-ver16
Maybe this wasn't a thing 13 years ago, but today with SSDT you can export a sql server database under 200GB using Sqlpackage Export functionality:
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?view=sql-server-ver16
Then you can import a bacpac using the SqlPackage Import functionality
https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-import?view=sql-server-ver16
试试这个:
1)在您的计算机中共享具有完全权限的文件夹
2)在您的SQL服务器中:
控制面板->管理工具->服务->右键单击
登录选项卡上的所有 SQL 服务应以域管理员身份启动
3) 在 sql server 维护向导中放置备份位置和文件夹 (\yourcomputername\sharedfoldernam)
我在 sql server 2008 的 8 个服务器上进行了远程备份公司
just try this one:
1)Share a folder with full permission in your computer
2) in your SQL server :
control panel -> administrative tools -> services -> right click on all SQL services
on log on tab should start with your domain administrator
3) in maintenance wizard of sql server place the back up location and folder (\yourcomputername\sharedfoldernam)
I did remote backup on 8 server of sql server 2008 in our company
我可以这样做一次...要做到这一点,你必须在远程服务器上打开一个共享。然后您可以直接将备份放在共享本身上,而不是默认位置...
通常管理员会进行备份并在某个共享文件夹中与我们共享。我尝试过如果我将备份放在那里是否可行。它起作用了。
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.
如果您使用 SSMS 下的“生成脚本”,请单击“高级”按钮。在“为依赖对象生成脚本”选项下,单击“True”。通过单击,每个对象的任何依赖项也将按正确的顺序编写脚本。
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.
某些第三方备份程序允许设置具有特定网络权限的文件传输。当 SQL Server 服务在受限帐户下运行并且没有足够的网络权限时,它非常有用。尝试使用 EMS SQL Backup 来解决此任务。
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.
为此,我使用 Redgate backup pro 7 工具。您可以在其他位置创建磁贴中从备份文件创建镜像。并可以在创建后自动将备份文件复制到网络和主机存储上。
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.