grjitdept
asked on
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?
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'
chapmandew
Have you tried the wizard that MS provides for shipping logs? That may be of some assistance.
BTW, does this work for you? I excluded the undo file.
RESTORE LOG DatabaseName FROM
DISK = N'C:\Transaction Logs\DB\DB_tog_20071130000
WITH FILE = 1, NOUNLOAD , STATS = 10,
STANDBY
RESTORE LOG DatabaseName FROM
DISK = N'C:\Transaction Logs\DB\DB_tog_20071130000
WITH FILE = 1, NOUNLOAD , STATS = 10,
STANDBY
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.
You can restore with NORECOVERY AND APPLY the required locks but you can't read the db during this process like STANDBY mode.
ASKER
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...
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.
ASKER
So basically I can't do what I thought I could - this will have to be done by transactional replication
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.