Restoring SQL2000 Transaction Logs to SQL2005

Hey all,

Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)

When I attempt to restore either a FULL backup or transaction log I get the error:

RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.

Anybody have any thoughts how I might be able to get around this?

Thanks

Eric

The physical structure of the database has changed between SQL2000 and SQL 2005. Since the transaction logs contain low-level data, you cannot apply SQL 2000 transaction logs to a SQL 2005 database, or vice-versa

|||

Bummer, ya figure MS would build the 2005 transactional restore with a backward compatibility method, (in the since of restoring a database in STANDYBY mode) though what’s the point of keeping a database in (80) SQL 2000 mode if it truly is a semi-upgraded 2005 database.
Please correct me if I am wrong here. but I understand the reason why the 2000 versioned database is it can't be restored in stand by mode without created a log of is own during the upgrade.( not to repeat myself but if the destination database is in SQL 2000 format yet is attached to a SQL 2005 instance) it shouldn't have a issue, that is if SQL 2005 was truly backward compatible.

Thanks

|||

OK, There are several things here, and I'll try to address them all.

First off, there is a common misconception about compatibility modes and what they are.

Setting a compatibility mode governs how TSQL is interpereted and behaves, but does NOT have any impact to the physical structure of the database itself. So, a SQL 2005 database in 80 compatibility mode will have a SQL 2005 structure in the database files, but will respond to TSQL as if it were a SQL 2000 database. Meaning that any deprecated/removed keywords will be honored, and any behaviors which have been changed will act as they did in SQL 2000.

When you upgrade to SQL 2005, the database structures are upgraded at the point in time when the database is recovered.

So, you can continue to apply SQL 2000-based logs to the database as long as it is never recovered. Unfortunately, you cannot access it for any purpose other than applying logs. You cannot put it in standby mode, as that implies recovering the database, which triggers the update. You also cannot create a database snapshot without recovering/upgrading the database.

|||

Hmm, well that make since. i was a little shady on the compatibility modes though if the database structure does not change, they whats the big deal with attaching a once sql 2000 database to a 2005 instance and restoring a sql 2000 transaction file to it. ( during the attach process does the database automatically get upgraded?)

Thanks

|||

Part of attaching a database involves recovering it. This triggers the upgrading of the structures.

There is currently not a CREATE DATABASE FOR ATTACH WITH NORECOVERY.