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'
精彩评论