Restoring SQL 7 backups to SQL 2000

Question

Post reply

Restoring SQL 7 backups to SQL 2000

PurpleLady

SSCertifiable

Points: 7819
More actions
March 24, 2003 at 7:41 am

#105895

Good morning. I think I already know the answer to the question but will ask anyway. Is it possible to restore a SQL 7 backup to SQL 2000 and have the database remain in standby mode? I am - correction was - restoring a full backup of an 18G database weekly, and then applying the transaction log backups daily. However, the provider is on SQL 7 and we just converted to SQL 2000.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You can't restore from SQL 7 to 2000.

You can upgrade to 2000 and restore or script the db, and create a DTS package (or use bcp) to move the data.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

You can restore a SQL 7 Backup to a SQL 2000 server (I do this almost every day from my prod 7 server to my local test 2K server) and keep it in standby. You just cannot restore the other way around.

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 3

Ditto with Antares.

You can even move the MDF's and attach.

KlK, MCSE

KlK

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

Antares is very correct as I do this as well. The only db's that can't be restored / upgraded are the master and the msdb (learned this the hard way).

Rita's question was whether the database could stay in standby mode and from what I see in BOL that will not work when the restore is from a previous version. From BOL "STANDBY is not allowed when a database upgrade is necessary."

Don't have the test area that I need at the moment so, that is the best answer that I can give.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

PurpleLady SSCertifiable Points: 7819 More actions · Answer 5

Thanks everyone. I've rescripted the restores to use the NORECOVERY and RECOVERY options along with setting the database to read-only once the restores are completed. I was worried that because of the repitition, we would run into our daily usage window, but so far things are running well.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 6

You are right guys, my apologize for the mistake and the confusion.

Siempre es bueno reconocer los errores.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Sorry, my brain just isn't glued in place this week. You can do a full restore but the upgrade of the DB to the 2000 format does require no standby. Again that was my error and I appologize.