开发者

SQL Server: force Backup/Restore location

Took a copy of a database of SQL Server Express on a laptop using the BACKUP DATABASE command, now I'm trying to restore it to a different computer using the T-SQL statement:

RESTORE DATABASE [OurDB]
FROM DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part1.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part2.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part3.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part4.bak',
DISK = N'C:\Documents and Settings\开发者_StackOverflow中文版Sam\Desktop\DBBackup\part5.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part6.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part7.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part8.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part9.bak',
DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part10.bak'
WITH REPLACE
GO

But when it tries to restore I get the error

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AppConfig_Data.ndf" failed with the operating system error 3 (failed to retrieve text for this error. Reason: 1815).

And a couple of others, all similar errors. The problem is it looks like SQL Server is trying to restore the database to the directory C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, the data directory on the source computer the backup was taken from.

But on the new computer I want to restore to the SQL Server data path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

How do I change the path in the data-files to get it to restore to the correct location?


You need to add additional info to your RESTORE command to define where those bits should go - something like:

RESTORE DATABASE [OurDB]
FROM DISK = N'C:\Documents and Settings\Sam\Desktop\DBBackup\part1.bak',
MOVE N'Your_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\OurDB.mdf',  
MOVE N'Your_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\OurDB_Log.ldf'
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜