Restore database in SQL Server 2005

Question

BACKUP DATABASE [MPRM] TO  DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH NOFORMAT, NOINIT,  NAME = N'MPRM-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The backup process worked, and I got a file named MPRM_15_5_10.BAK in my shared folder (D:\shared\). This is a backup created from another machine.

When I try to restore the backup, using the following script

RESTORE DATABASE [MPRM] 
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH FILE = 1,  NOUNLOAD, STATS = 10

I get the following errors

Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\DATABASES\MPRM.mdf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'MPRM' cannot be restored to 'E:\DATABASES\MPRM.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 "E:\DATABASES\MPRM_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
Msg 3156, Level 16, State 3, Line 1
File 'MPRM_log' cannot be restored to 'E:\DATABASES\MPRM_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.

Why the system asks about *.mdf, *.ldf files ? Is it anything related with Backup option rather than Restore script ?

I logged in with Windows Authentication

You restore over an existing database or the database not exist? First case you need to set overwrite option. I don't use command line myself (use Backup Agent from the Management Studio), but I guess you need to specify the target location of the file while restoring it, especially if you target loc is different from the original backup path. — YvesR
It's trying to restore it into the same path as it was on the other machine. And the messages tell you that if you want it to use different paths, you need to use the WITH MOVE option — Damien_The_Unbeliever
@Damien_The_Unbeliever No. It will restore to [MPRM] from MPRM_15_5_10.BAK. Right ? — Rauf
@Rauf: yes - but where will the actual physical files (the .mdf and .ldf) go?? That's your problem - it tries to re-create the files in the exact same location where they were on the source system where you took the backup - but that drive/directory doesn't exist on the target system where you're trying to restore it to — marc_s

marc_s marc_s · Accepted Answer · 2012-04-29T10:07:44

You need to specify where to store the physical files when you restore a .bak if your target server doesn't have the same disk/directory layout as the original source server. The backup file contains the logical SQL Server files along with the original location on the source server (the full physical path where the .mdf and .ldf where located).

So you need to use something like this:

RESTORE DATABASE [MPRM] 
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK' 
WITH FILE = 1,  
MOVE N'MPRM' TO N'D:\MSSQL\Data\MPRM.mdf',  
MOVE N'MPRM_Log' TO N'D:\MSSQL\Data\MPRM_Log.ldf',  
NOUNLOAD, REPLACE,  
STATS = 10

This command here:

MOVE N'MPRM' TO N'D:\MSSQL\Data\MPRM.mdf',

specifies that the logical file called MPRM (that's the default when you didn't specify anything else when creating your SQL Server database) should be moved during restore to the physical location D:\MSSQL\Data\MPRM.mdf (adapt this as needed)

To just see what is contained inside a backup file, you can use this command here:

RESTORE FILELISTONLY
FROM DISK = N'\\rauf\shared\MPRM_15_5_10.BAK'

This will show you all the logical files inside your backup, along with their original physical file that they were backed up from (on the source server, where you ran the backup command).

Restore database in SQL Server 2005

1 Answers