使用正确的逻辑文件名还原SQL DB [英] Restore SQL DB with correct logical file names
问题描述
我打算在另一台服务器上自动恢复数据库的数据库
有人可以用SQL指出我正确的方向脚本要
将逻辑文件名修改为正确的路径，而不是那些用DB继承的
??
即数据库将在新服务器上重命名
任何帮助非常感谢
非常感谢提前
推荐答案
blueboy写道：
blueboy wrote:
我计划在另一台服务器上自动执行数据库的夜间恢复
有人可以用SQL脚本指向我正确的方向
修改逻辑文件名到正确的路径，而不是那些
随着DB继续运行？
即数据库将在新服务器上重命名
任何帮助非常感谢
非常感谢dvance
Hi,
I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??
i.e the database is to be renamed on the new server
any help much appreciated
Many thanks in advance
这应该指向正确的方向：
http://support.microsoft.com/default...b;en-us;314546
如果将数据库还原到与源
数据库不同的文件位置，则必须指定WITH MOVE选项。例如，在
源服务器上，数据库位于D：\Mssql \ Data文件夹中。
目标服务器没有D盘，你想将
数据库恢复到C：\ Mssql \ Data文件夹。
< br $>
祝你好运
This should point you in the right direction:
http://support.microsoft.com/default...b;en-us;314546
If you restore the database to a different file location than the source
database, you must specify the WITH MOVE option. For example, on the
source server the database is in the D:\Mssql\Data folder. The
destination server does not have a D drive, and you want to restore the
database to the C:\Mssql\Data folder.
Good luck
非常感谢你似乎是我的追求然而我保持
收到错误 -
工作失败了。作业由用户domainname\user调用。
运行的最后一步是第2步（恢复）。这项工作被要求在第1步开始
（杀死连接）。
这里是我的脚本;
杀死连接 -
ALTER DATABASE {db name} SET SINGLE_USER立即回滚
恢复 -
RESTORE DATABASE {db name}
来自DISK =
''E：\ folde \ {db name} .bak''
WITH MOVE''Logical_Name_Data''TO''G：\ SQLDATA \ MSSQL \ data \ {db name}
_Data.MDF''，
MOVE''Logical_Data_Log''TO''G：\ SQLDATA \ MSSQL \ data \ {db name}
_Log.LDF''，
STATS = 1，更换
GO
它在第2步停止我还注意到当我回到他们的步骤时他们是b / b
违约回到主数据库??
任何帮助非常感谢
Many thanks for that it seems to be what i was after however i keep
getting an error -
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).
here is the scripting i have;
kill connections -
ALTER DATABASE {db name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Restore -
RESTORE DATABASE {db name}
FROM DISK =
''E:\folde\{db name} .bak''
WITH MOVE ''Logical_Name_Data'' TO ''G:\SQLDATA\MSSQL\data\{db name}
_Data.MDF'',
MOVE ''Logical_Data_Log'' TO ''G:\SQLDATA\MSSQL\data\{db name}
_Log.LDF'',
STATS = 1, REPLACE
GO
It stops at step 2 i also notice when i go back into the steps they
are defaulting back to the master database??
Any help much appreciated
blueboy（ma ******** @ hotmail.com）写道：
blueboy (ma********@hotmail.com) writes:
非常感谢，因为它似乎是我所追求的但是我保持
收到错误 -
工作失败了。作业由用户domainname\user调用。
运行的最后一步是第2步（恢复）。要求在第1步开始工作
（杀死连接）。
Many thanks for that it seems to be what i was after however i keep
getting an error -
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).
您是否在工作历史记录下查看失败的原因？最右边的
（在SQL 2000的企业管理器中），有一个复选框，上面写着查看
步骤历史记录。应该有一条错误消息。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Did you look under Job history to see what failed? Up to the right
(in Enterprise Manager in SQL 2000), there is a checkbox which says "View
step history". There should be an error message.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于使用正确的逻辑文件名还原SQL DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
