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?
 
chapmandewConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
All Courses

From novice to tech pro — start learning today.