从另一台机器上的.bak文件还原数据库 [英] Restoring a database from .bak file on another machine
问题描述
我没有做太多的SQL，还是很新的，所以请原谅这可能是一个基本的问题。
I've not done much SQL and am still pretty new to this, so please excuse what's probably a basic question.
我被要求调查创建一个SQL作业来备份我们的数据库，将.baks存储在另一台机器上，然后将它们还原到第二台服务器。我一直在做一点研究和玩SSMS，并通过设置共享和运行备份作业到共享位置备份数据库到我的个人机器。我现在正试图创建一个新的数据库（在同一台服务器上我从上面恢复）通过恢复.bak文件（但给数据库我试图创建一个新的名称，什么是非），但不能指定从共享恢复它，就像我在备份它/我不能找到如何指定其他网络位置，我只是浏览服务器的C盘，当我尝试找到该文件。
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.
现在，我只是使用内置的向导来尝试并实现这一点（打开SSMS - >连接到服务器 - >右键单击DataBases - >恢复数据库，然后选择从设备和浏览找到文件）。
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).
这不是最后的过程，只是我试图抓住这个工作原理。正如我所说，这个想法是最终有一个计划的工作，将DB从server1备份到.bak上，比如我的个人计算机，然后恢复到server2上的DB（不同的网络，不同的城市），并可能，使用一系列SQL命令，而不是每次都使用向导（有几个DB，最终需要备份）。
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).
对于可能的问题，我非常抱歉，基本上，我需要知道的是我可以/如何恢复SSMS中的DB从.bak在不同的机器上？
非常感谢
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
推荐答案
您可以使用类似下面的脚本。它从文件系统中恢复数据库，并使用名称MyDB覆盖现有数据库，将文件移动到进程中选择的新位置。
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.
RESTORE DATABASE
MyDB
FROM DISK = '\\MyShare\MyBackup.bak'
WITH
MOVE 'DataFile' TO 'D:\myNewDBLocation\DataFile.mdf',
MOVE 'LogFile' TO 'E:\\myNewDBLocation\LogFile.ldf'
, REPLACE
您可以找到lical文件的名称（在上面，这些名称 DataFile 和 LogFile ：
You can find out the name of the llogical files (in the above, those are called DataFile and LogFile by running the following:
RESTORE FILELISTONLY
FROM DISK = '\\MyShare\MyBackup.bak'
有关各种选项和参数的其他信息：
Additional information about various options and parameters:
这篇关于从另一台机器上的.bak文件还原数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
