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.

Restoring SQL2000 bkp file on SQL2005

Is it possible to restore SQL2000 backup to SQL2005? Or I have to restore the DB to SQL2000 and then upgrade it to SQL2005?

Can someone provide me ways to upgrade SQL2000 DB to SQL2005?

Thanks,

Hiten

Simply restore it on the SQL 2k5 server. Backups of 2k are compatible with SQL2k5 (but not vice versa). You have to be aware that the internal (meta) structure of the database is upgraded during the restore process and therefore can′t be backuped and restored on the SQL2k after the upgrade.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Is this possible on SQL 2005 Express too? Or I need to have SQL server 2005 ?|||That also valid for SQL Server 2k5 Express.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Restoring SQL2000 after upgrade

I performed an in-place upgrade of my SQL2000 database to SQL2005. I am having too many problems at the moment with compatability issues between SQL 2005 and VS2003 and need to roll back SQL 9.0 to 8.0. Are there any white papers, or published procedures for doing this? I have a tape backup of the 2000 system, but there have been various updates to the databases during the past 3 weeks since the upgrade. The structure of the databases have not changed.

Please help!

There is no vehicle to rollback. You will want to reinstall sql2k as a different instance, restore data from your tape backup and then use data import/export to update sql2k data with sql2k5 data.

If you're looking for a way to mount a sql2k5 database on a sql2k instance, it's not possible.|||

Are you running the SQL Server database in 9.0 or 8.0 compatibility mode? When you upgrade from 2000 to 2005, you will be in 8.0 mode unless you explicitly changed it to 9.0. If you are running in 9.0 mode and having problems you might try switching back to 8.0 and see if that helps as a temporary measure. What kind of problems are you having?

If you are already in 8.0 mode and still having problems that you cannot work through, then I would install a fresh copy of SQL Server 2000 (on a different server), apply SP4 and any hotfixes, and then restore from your tape backup. Then, you can use SSMS from SQL Server 2005 to connect to both servers are start trying to get your data back in synch on your three week old backup.

You can use "DataDude" (Visual Studio 2005 Team Edition for Database Professionals) to to compare the data between the two databases, and generate scripts to help you get the old copy up to date.

|||

I am running in 9.0 mode. The main problem is that all my applications are written in VS2003 and when I need to change a data adapter, I have to manually code the insert/update and delete methods rather than having VS generate the automatic code, which is a pain.

These applications manage extensive databases and there are probably a hundred data adapters involved, granted, most of them never change, but it makes maintenance a bear when they do. I know that the DB operations are generally more efficient if you code those methods manually, but the applications are used by only a handfull of people (20 or so) and I doubt the increased processing efficiency would be noticable vs. the ease of letting VB auto-create the code. Plus, I think the 2000 enterprise manager is much more intutitive and much faster than the the SQL2005 studio. Call me old fashion, but it strikes me that the 2005 Studio has so many bells and whistles it's become bloated and sluggish.

I've now re-installed SQL2000 as a separate instance and applied SP4, restored the data from tape and am about to copy the 2005 data to 2000. When all is well, I'll uninstall 2005.

At some point I will bite the bullet and migrate my applications to VS2005 and then it will probably make sense to upgrade SQL to 2005 as well. For the moment, I'll stay a version behind. Thanks for your comments.

Restoring Databases between SQL2005 Express and Developer Editions

Hi,

Are there any issues between porting databases fom SQL2005 express to SQL2005 Developer?

I'm trying to move Adventure works between the two on my Laptop. Both instances were installed identically and in the same directory paths except express is in MSSQL.1 and Developer in MSSQL.2

If I try to backup from Express to Developer and restore in developer I get an error suggesting that the backup contains more than 1 database image. When I detach\attach from express to developer I get a

Msg 5133, level 16, State 1, Line 1

Directory look up fro the file 'c:\program files ....' failed with operating system error 123 (error not found)

Does anyone have any ideas?

Thanks in Advance

Dave

Yep. The database file locations are stored within the backup media, you will have to change the file location of the datafiles during the configuration of the restore (if you use the GUI to perform the restore) or use the WITH MOVE Option if using the TSQL command for restoring.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

restoring database leaves me with (Restoring...) message

I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)

Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.

Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.

Any advice on how to get this thing finished out?

can u post the script which u r used for backup and restore. did u use with Move option while restoring.

Madhu

|||

Hi,

try

restore database <yourdatabase> with recovery

and as suggested plese post the script you used to restore database.

Hemantgiri S. Goswami