Problem transfering data at the very end! 1

[skhoury]

Hello all,

Im having a slight complecation getting my data from one sqlserver 7 system to another. Here is the scenario:

We want to upgrade our DB server from NT4Svr/SQLSvr7 to Win2KSvr/SQLSvr7. In stead of doing an inplace upgrade, we are going to blow the box away and rebuild with 2K.

Naturaly we want to backup the data first so that we can restore it to the newly rebuilt server. The approach I thought about taking was to use the DTS to transfer the various DB's over to a temporary PC setup with SQL7. I selected the "Transfer objects and data between SQL Server 7.0 databases"

The problem is this: Everything appears to be going well, then right at the very end this error appears:

"Failed to transfer objects from MS SQL Server to MS SQL Server" ...then I click 'OK'.

and double click the item in the DTS status window and get this:

"[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_PF_Actions_PF_Contracts'. The conflict occurred in database 'ContractsMngr', table 'PF_Contracts', column 'ActivityNumber'."

Any thoughts anyone? Or even better, does any have a better idea for getting the data et all on the newly rebuilt server?

Many thanks in advance!

Salim

 

[JayKusch]

Try backing the databases up and restoring them to the new server.

Thanks

J. Kusch

 

[skhoury]

J,

I actually did that, and it works out just fine for all the DB's except one of them!....as soon as I start to do the restore, it stops and says this:

MS SQL-DMO (ODBC SQLState:42000)
"The file '..the path on the old server..' 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."

The funny thing is, that all the other DB's lived in the same location and didnt complain during the restore...just this one.

Anythoughts on this "WITH MOVE" option? Or how to get around this error?

Many thanks!

Salim

 

[JayKusch]

Try restoring that DB using Enterprise Manager(EM). Here is what we do ... On the Restore Server, if a DB shell has not been created, create the empty DB on the server. If the DB already exists in some form or fashion on the Restore server you do not need to create the shell. Once in place, right-click on the DB and choose "All Task".."Restore DB".

Choose the radio button "From Device" then click the "Select Devices" button. On this screen choose the "From Disk" radio button. Click "Add" then point to the backup file on your disk. Click OK ...

Now choose the "Options" tab. Click the "force restore over existing DB" and here is where are problem lies ...

You need to change "Move to physical file name" for the MDF and LDF to match the path of where the "shell or existing" DB is located. The path that is currently displayed probably does not exist on the Restore server OR the path listed does not correctly point to the MDF and LDF of the DB you are trying to restore.

Let us know how this works out!

Thanks

J. Kusch

 

[skhoury]

J,

THANK YOU!!! Your idea worked perfectly!! First thing I did was create an empty DB with the exact same name as the original DB. Then I select the restore option, found the file...and here is the kicker, modified the "Move to physical file name" parameter to match the location of the MDF and LDF files (the ones I created with the empty DB).

The restore succeeded!

Thanks so much!

Salim

 

[JayKusch]

Perfect ... exactly as I said ...

"Move to physical file name" for the MDF and LDF to match the path of where the "shell or existing" DB is located.

Thanks

J. Kusch