I am trying to restore database and it fails with error with 2 /3 options tried
RESTORE DATABASE abcdDb
FILE = N'abcdDb'
FROM DISK = N'i:\Data\abcdDb.bak'
Fails with Error
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb.mdf"
failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'abcdDb' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb.mdf'.
Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file
"C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb_log.ldf"
failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'abcdDb_log' cannot be restored to
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
RESTORE FILELISTONLY
FROM DISK = 'i:\Data\abcdDb.bak'
Gives me output as follows
abcdDb C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb.mdf D
abcdDb_log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb_log.ldf L
Used above file location to restore as below
IF DB_ID('abcdDb') IS NULL
BEGIN
RESTORE DATABASE abcdDb
FILE = N'abcdDb'
FROM DISK = N'i:\Data\abcdDb.bak'
WITH
FILE = 1, NOUNLOAD, STATS = 10,
MOVE N'YC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb.mdf'
TO N'C:\Data\abcdDb.mdf',
MOVE N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb_log.ldf'
TO N'C:\Data\abcdDb.LDF'
END
(2 row(s) affected)
Msg 3234, Level 16, State 2, Line 6
Logical file 'YC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\abcdDb.mdf' is not part of database 'abcdDb'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
