Restore from Enterprise to Standard : Missing sp_

  • I'm rehearsing for a server move and am encountering something I'd like to avoid when showtime arrives.

    I am moving operations to a new SQL Server 2000 box on a different network. I have downgraded from the Enterprise edition to Standard b/c I don't need all the benefits of that version.

    Here are the steps I am taking.

    I ftp the .BAK to the new box.

    Right click Databases in EM and choose 'All Tasks'

    Enter the new db name in the Restore as Input box.

    Select 'Restore From Device'

    Backout to main screen and change the drive letter to the dirve on the new box where mdf and ldf will be stored.

    Choose Leave database operational and click ok.

    The Restore begins and after a few minutes ends with this popup message:

     

    Could not find Store Procedure 'dbo.sp_MSrepl_backup_start'.

    Could not adjust the replication state of database 'newdb'. The database was successfully restored, however its replication state is indeterminate. See the Troubleshooting Replication section in Books Online. RESTORE DATABASE successfully processed 77625 pages in 57.733 seconds.

    I then close EM and repoen it to find the DB there with all its tables and data. Any idea why this is happening?

     

    Thanks!

    Befuddled

  • I have checked on a "Standard" edition copy of SQL and this sp does exist (it should be in sp's under master database) - you might want to check if your copy exists - if it does it may be some permission issue.

    I think this SP is used to update the status of replication in a database that has been restored - is the database you are restoring a replication distributor?

    What you might find is quicker than backup/restore is to take the "source" database offline, copy the MDF and LDF files to your new box and then just choose the "attach database" option to reaatch them.

    I assume you have a reason for using ftp to get them across but FTP is not a fast option ... not sure how big the database is but a network copy using a mapped drive letter and xcopy is pretty fast (faste than to a \\server\share unc) - for real big ones unless you have a GB network one useful way is to use one of the now common USB2 external drives (Maxtor, Lacie etc) to the db to - then just attach this to the new box and copy it off - nice and fast and you can walk round your firewall etc if you have to

  • THanks for the reply. When I delete the DB on the new server and then REBOOT prior to carrying out the restore steps all goes well without the error message.

    If I take the DB offline and use the mdf and ldf, will all the data be there up to the time that the connection was broken? To reattach should the db already exist?

    I checked my Master's table SPs and wasn't able to find 'dbo.sp_MSrepl_backup_start'

    Is there any reason this might have been removed by my hosting company?

    Thanks for the input!

     

    Rich

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply