RESTORE WITH MOVE
If you have taken backup from one server and are going to restore on another server, but the problem is on New server the same directory structure or even the drive is not available as of the one from where the backup was taken.. In that case you will get error on restoring as SQL Server tries to create files on same locations.
To get rid of this situation use WITH MOVE command with your RESTORE as shown below:
RESTORE Database [NewWorkDB]
FROM DISK = ‘E:\WorkDB.bak’
WITH MOVE ‘Test’ to ‘E:\NewWorkDB.mdf’, –Test is logical name of datafile
MOVE ‘Test_log’ to ‘E:\NewWorkDB_log.ldf’ –Test is logcal name of log file
To get list of files being restored prior to perform a RESTORE you can use FILELISTONLY command.