Restoring a backup of a database

[TomBarrand]

I have backed up a database on one server and would like to restore the backup into a new database on another server, but I get the following message. I have copied the backup file from the backup directory on one server and paste it into the backup directory on the other server.

The file e:\mssql7\data\Test_Data.MDF cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.

Backup or restore operation terminating abnormally

Thanks,

Tom

 

[fluteplr]

Click on the options page and change the file locations. The backup stores the original file locations and by default puts the file back where it came from. If the new server does not have the same directory structure or drive letters you will get that error.

 

[jkb17]

Use the enterprise manager and click on the options tab of the Restore Database window.

You will see that the old path (the path of the .mdf and .ldf file) are related to the original database. So, if your data files were stored on a server with an E:\ and the new server has a d:\ you have to go in and type the new path. (THis is essentially typeing the RESTORE DATABASE command with the WITH MOVE option).

You are most likely overlaying this new dummy/blank DB on the new server. So, what you want to do is change the paths of the .ldf and .mdf files to overwrite the empty, default 1MB files created by the new database. This will take your existing DB Backup file and move them into 2 totally new files. Also, check the check box that says "Force restore over existing database." This will completely eliminate the dummy\blank db and replace it with new files saved in the backup copy.

You can't change the logical name of the files - just the physical locations and physical file names.

If you're curious as to the SQL syntax: just before you click the OK button to start the restore, start up your Query Analyzer. You'll see exactly what Enterprise Manager creates as SQL for your operation.

My explanation may be a little choppy since I don't have the Enterprise Manager open in front of me. If you need further clarification then just respond and I will write a more detailed explanation (with the Enterprise Manager open in front of me).

good luck