May 18, 2006 at 11:39 am
Log Shipping between SQL Server 2000 as source and SQL Server 2005 as destination does'nt work.
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. [SQLSTATE HY000] (Error 62309). The step failed.
Any tricks/workarounds?
May 19, 2006 at 9:00 am
Just went through migrating to SQL 2005, and maybe this info will help:
We use third party backup tools (a la SQL LiteSpeed) to perform log shipping, but the process is the same concept as always: Full backup on primary server, restore to secondary server, then regular transaction log backups applied to the secondary server. In our process, we brought the database into STANDBY recovery mode after each transaction log restore; and we ran into the same problem you described. So...
During the recovery phase on the secondary server, don't specify 'STANDBY' as the recovery mode, specify 'NORECOVERY'. This doesn't help if you allow people to read the database on the secondary server, as the DB will not be readable, but it will allow log shipping to take place. Once you specify the "final" log ship, and specify that the secondary database is recovering, it will perform the database upgrade at that moment.
This may or may not apply to your specific situation, but hopefully it will help.
October 19, 2006 at 3:25 pm
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 same error that bhattpranav got:
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
October 19, 2006 at 6:46 pm
We had same issue- there is no way (I read in BOL) to create standby server in case of primary and secondary servers have different versions (like 2K and 2K5)
October 22, 2006 at 9:50 am
You can have Log-shipping between SQL 2000 and SQL 2005. BUT you cannot keep secondary in STANDBY mode.
Tools (Enterprise Manager / Management studio) cannot be used to set it up.
When we try to setup the Log-shipping between SQL Server 2000 and SQL Server 2005 from Enterprise Manager we are unable to view SQL Server 2005 Server Name from Add Destination Database dialog box of Maintenance plan and similarly when we try to choose Ship Transaction logs task from any of the SQL Server 2005 database to setup log-shipping using SQL Server 2005 database as primary database, we do get an error popup window with below message if we try to connect to SQL Server 2000 Server to choose this server as secondary server.
Error Message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
‘.’ is not a valid secondary server instance because it is not the correct version.
Secondary server instances must be servers running SQL Server 2005 or later.
------------------------------
BUTTONS:
OK
------------------------------
In conclusion it’s not possible from either Enterprise Manager and Management Studio interface.
When setting up logshipping, it is not possible to set SQL Server 2005 as Primary and SQL Server 2000 as secondary due to the fact that transactions logs from SQL Server 2005 can’t be restored on SQL Server 2000. So option of Logshipping from SQL Server 2005 to SQL Server 2000 is ruled out.
Coming to logshipping from SQL Server 2000 to SQL Server 2005, we can’t restore a database from SQL Server 2000 to SQL Server 2005 with a standby option enabled. If done so, it throws exception ad below:
Server: Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is
needed. Reissue the RESTORE without WITH STANDBY.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Only way left to configure is to have SQL Server 2000 as Primary and SQL Server 2005 as secondary with norecovery. Also please not due to afore said reasons automatic failover of logshipped database is not possible. It could only be used as a remote mirror (not exactly) where database is in norecovery mode and when we need we can failover manually.
To do the above, we need to manually create jobs that
• Take transaction log backups (SQL Job).
• Copy transaction log jobs (XP_cmdshell)
• Restore it on secondary server with no recovery (SQL Job)
• Clean jobs
Also if we look at that documentation from BOL which talks on “Upgrading a SQL Server 2000 Log Shipping Configuration” we cannot directly upgrade a SQL Server 2000 log shipping configuration to SQL Server 2005. The Database Maintenance Plan Wizard, which was integral to log shipping in SQL Server 2000, is not used as part of the log shipping configuration in SQL Server 2005. As a result, log shipping stops functioning when you upgrade a server to SQL Server 2005
June 16, 2007 at 9:28 pm
Hi Guyz,
y do u need to go for logshipping b/w sql 2000 to sql 2k5.......it doesnt server the purpose of logshipping by confuguring it b/w 2000 to sql 2005 as destination.u configure logshipping to ensure high availability of ur DB...but if ur destination in 2k5 theres no point in configuring it......as many outdated features are unavailable in sql 2005......refer the below link
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm
moreover u need to redirect ur apps during failover ....in such scenarios how will it work in 2k5??????? hence better go for 2k to 2k or 2k5 to 2k5.......may be in replication u can hav the subscriber as 2k if pub is 2k5...
[font="Verdana"]- Deepak[/font]
April 9, 2008 at 10:59 am
I have been receiving a message similar to the ones above for months now. I am log shipping between two SQL2000 boxes using litespeed.
SQL Server has returned a failure message to LiteSpeed which has prevented the operation from succeeding. The following message is not a LiteSpeed message. Please refer to SQL Server books online or Microsoft technical support for a solution: Deleting database file 'e:\data\db_name_undo.dat'. Processed 457160 pages for database 'db_name', file 'db_name_Log' on file 1. RESTORE LOG is terminating abnormally. [SQLSTATE HY000] (Error 62309). The step failed.
I am doing the extact same thing with another database between the same servers and I have had no problems. So I know that the connection between the two servers is good.
June 3, 2008 at 2:05 pm
Hi Mark
from previous experience when I get the HY000 error its telling me it cannot extend the ttansaction log any further. However on one of the sites I look after its does this even when the partition has 63GB free space left with no quota restrictions enabled.
Truncating the logfile and shrinking it does the trick.
It might be something to look at.
hth
john
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply