SQL 从另一台机器上的 .bak 文件恢复数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13396027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Restoring a database from .bak file on another machine
提问by cprlkleg
I've not done much SQL and am still pretty new to this, so please excuse what's probably a basic question.
我没有做过太多的 SQL 并且对此还很陌生，所以请原谅可能是一个基本问题。
I've been asked to look into creating an SQL job to backup our databases, store the .baks on another machine and then to restore them to a second server. I've been doing a bit of research and playing with SSMS and have back-ed up the database to my personal machine by setting up a share and running a backup job to the share location. I'm now trying to create a new database (on the same server I back-ed up from) by restoring the .bak file (but giving the database I'm trying to create a new name and what-not) but am unable to specify restoring it from the share, like I did when backing it up/I can't find how to specify other network locations and am just browsing the server's C drive when I try to locate the file.
我被要求研究创建一个 SQL 作业来备份我们的数据库，将 .baks 存储在另一台机器上，然后将它们恢复到第二台服务器。我一直在做一些研究并使用 SSMS，并通过设置共享并将备份作业运行到共享位置来将数据库备份到我的个人机器上。我现在正在尝试通过恢复 .bak 文件来创建一个新数据库（在我备份的同一台服务器上）（但给数据库我正在尝试创建一个新名称等等）但我无法指定从共享中恢复它，就像我在备份它时所做的那样/我找不到如何指定其他网络位置，当我尝试定位文件时，我只是在浏览服务器的 C 驱动器。
For now, I'm just using the built-in wizards to try and achieve this (open SSMS -> Connect to server -> right click DataBases -> Restore DataBases and then select From Device and browse to find the file).
现在，我只是使用内置向导来尝试实现这一点（打开 SSMS -> 连接到服务器 -> 右键单击数据库 -> 恢复数据库，然后选择从设备并浏览以查找文件）。
This isn't the final process, just me trying to get to grips with how this works. As I said, the idea is to ultimately have a scheduled job to backup the DB from server1 to a .bak on, say, my personal machine and then to restore that to a DB on server2 (different network, different city) and, probably, with a series of SQL commands rather than using the wizard every time (there are a few DBs that'll, ultimately, need backing up).
这不是最后的过程，只是我试图掌握它是如何工作的。正如我所说，这个想法是最终有一个预定的工作来将数据库从 server1 备份到 .bak 上，比如说，我的个人机器上，然后将它恢复到 server2 上的数据库（不同的网络，不同的城市），并且可能，使用一系列 SQL 命令而不是每次都使用向导（有几个 DB 最终需要备份）。
My apologies for the, possibly, quite drawn out and convoluted question - essentially, all I need to know is can I/how can I restore a DB in SSMS from a .bak on a different machine? Many thanks
我对这个可能相当冗长和令人费解的问题表示歉意 - 基本上，我需要知道的是我可以/如何从另一台机器上的 .bak 恢复 SSMS 中的数据库？非常感谢
回答by SchmitzIT
You could use something like the following script. It restores a database from the filesystem, and it overwrites the existing database with the name of "MyDB", moving the files to new locations of your choice in the process.
您可以使用类似以下脚本的内容。它从文件系统中恢复数据库，并用“MyDB”的名称覆盖现有数据库，在此过程中将文件移动到您选择的新位置。
RESTORE DATABASE
MyDB
FROM DISK = '\MyShare\MyBackup.bak'
WITH
MOVE 'DataFile' TO 'D:\myNewDBLocation\DataFile.mdf',
MOVE 'LogFile' TO 'E:\myNewDBLocation\LogFile.ldf'
, REPLACE
You can find out the name of the llogical files (in the above, those are called DataFileand LogFileby running the following:
您可以找到 llogical 文件的名称（在上面，它们被调用DataFile并LogFile通过运行以下命令：
RESTORE FILELISTONLY FROM DISK = '\MyShare\MyBackup.bak'
Additional information about various options and parameters:
有关各种选项和参数的附加信息：
