Transaction log shipping from SQL 2000 to 2005 - Issue with Database upgrade required

Some quick background - we have a remote server with SQL 2000, and a local server with SQL 2005.

Our local server is a reporting server that reports on a copy of the SQL database on the 2000 server (our 'live' database)

We're currently backing up the entire 2000 database each night, downloading it and restoring it onto the 2005 server each night, so our local copy of the live database is updated every 24 hours.

I'm looking at transaction log shipping as a way to automate this process to keep the local copy up to date throughout the day. However I'm having a lot of problems caused by going from 2000 to 2005.

Firstly, I begin with a complete backup of the remote/live database and restore that to the local 2005 server in Standby / READ ONLY mode, so it is read-only, but transaction logs can still be applied at regular intervals. However I get an error message saying a database upgrade is required.

I got round this by restoring the backup normally on our SQL 2005 server (RESTORE WITH RECOVERY). I then backed up again from this restored copy (thus making the backup file 2005 compatible) and restored this 2nd backup in the STANDBY/READ ONLY mode.

First problem solved - a bit of hassle, but you should only need to do this the once to get the process started...

The second task was then to get the transaction logs restored onto the read only database (we'll do this hourly).
A contact gave me the attached code (edited)  to schedule the restore of the latest transaction log from a fixed filename.
When I run this code though, I again get the error message:

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

If I run it without 'WITH STANDBY' though, it'll go out of standby and further transaction logs cannot be applied... Any ideas? How could I 'upgrade' a 200 transaction log to 2005?
RESTORE LOG [LOG NAME] FROM  
 
DISK = N'C:\Transaction Logs\DB\DB_tog_200711300000.TRN' 
 
WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  
 
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_DB20071130Restore.DAT'

Open in new window

LVL 1
grjitdeptAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Have  you tried the wizard that MS provides for shipping logs?  That may be of some assistance.
0
chapmandewCommented:
BTW, does this work for you?  I excluded the undo file.
      

RESTORE LOG DatabaseName FROM  

DISK = N'C:\Transaction Logs\DB\DB_tog_200711300000.TRN'
 
WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,  
 
STANDBY
0
MohammedUCommented:
As per BOL: STANDBY is also not allowed when a database upgrade is necessary. Mean you can put the standby mode when you restore the backup of sql 2000 on to 2005 becuase there are many objects need to be created when it gets upgraded...
You can restore with NORECOVERY AND APPLY the required locks but you can't read the db during this process like STANDBY mode.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

grjitdeptAuthor Commented:
When you say you cannot read from the database in NORECOVERY and APPLY mode, does this mean you can NEVER read from the database, or just during each application of the transaction logs? If it was jsut a few minutes every hour that we were unable to query the database, that would be fine...
0
chapmandewCommented:
With NORECOVERY, you cannot read from the db...it is only there to apply new log files and is ready to be recovered when necessary.  In STANDBY mode, you can read from the database, as long as there is not a log file being applied.
0
grjitdeptAuthor Commented:
So basically I can't do what I thought I could - this will have to be done by transactional replication
0
chapmandewCommented:
Yeah, it looks like the different versions are really going to be a pain for you...unfortunately.  

One last thought...have you tried restoring with norecovery, then immediately changing it to standby?  So, something like this:

restore log dbname from disk = 'filepath' with norecovery
restore database dbname with standby

might be worth a shot.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.