restoring database to a different server

[Todd Minifie]

i'm attempting to restore a 7.0 database to a new server useing
the sql server enterprise manager. If i choose to restore from
device, i get an ODBC SQL State 42000 error and a suggestion to use the
"WITH MOVE OPTION". If i choose to restore via the database option, it
doesn't recognize my backup file in the dropdown list of backup files
to restore. I would like to use the enterprise manager if possible.
Any suggestions would be appreciated.

[Junjun Olympia]

You almost got it right when you chose to restore from device. A little tweaking is all that's needed. Now, go through the whole process again of Restoring from device. But before you click on Ok to start restore, click on the Options tab. Here you'll find the logical to physical file mapping of the database files (this usually makes sense if your restoring to a different db name). Try clicking on the Read from Media button to correct the mappings. If the mappings are ok, then click on the Ok button. Your database should restore smoothly.

[Todd Minifie]

thank you for the response, your suggestion works for me but my client is still getting the following error message: ODBC SQL State 42000 error and a suggestion to use the "WITH MOVE OPTION".

any further suggestions?

------------
Junjun Olympia at 9/10/99 3:33:32 PM

You almost got it right when you chose to restore from device. A little tweaking is all that's needed. Now, go through the whole process again of Restoring from device. But before you click on Ok to start restore, click on the Options tab. Here you'll find the logical to physical file mapping of the database files (this usually makes sense if your restoring to a different db name). Try clicking on the Read from Media button to correct the mappings. If the mappings are ok, then click on the Ok button. Your database should restore smoothly.

[Diana Williams]

I restore to a standby server every night. The way to do it is this:
On the server you want to restore to, map a drive to the server you are restoring from. I do this with the Net Use command. Your NT Admin should be able to help you with this.

Now go back to the restore dialog box, select restore "from device". In the next window in the box where it asks for device name, click on Add. In the next window, under file name (not device name) put in the full path name of where the backup device or file is using the mapped drive letter (example--I map the production server to the standby server with drive 'x'. My path name is x:serverdatamssql7dataackupCallData.BAK). Then click on out with OK's. Make sure you go into the "Options" tab and choose "Restore over" and "Leave operational". If using SQL statements, you will need to list each logical name and physical name of your files in the move statement (don't forget your log files.) BOL has a sample statement under 'moving files'.


------------
Todd Minifie at 9/10/99 4:14:39 PM


thank you for the response, your suggestion works for me but my client is still getting the following error message: ODBC SQL State 42000 error and a suggestion to use the "WITH MOVE OPTION".

any further suggestions?

------------
Junjun Olympia at 9/10/99 3:33:32 PM

You almost got it right when you chose to restore from device. A little tweaking is all that's needed. Now, go through the whole process again of Restoring from device. But before you click on Ok to start restore, click on the Options tab. Here you'll find the logical to physical file mapping of the database files (this usually makes sense if your restoring to a different db name). Try clicking on the Read from Media button to correct the mappings. If the mappings are ok, then click on the Ok button. Your database should restore smoothly.