November 9, 2012 at 11:55 am
Hi guys, I have an issue I hope you can help with. I'm hoping I don't know the answer already...
I have a SQL 2005 database (SP3) and a SQL 2008 R2 database. I'm trying to setup database log shipping from SQL2005.DBNAME to SQL2008R2.DBNAME and I have it all working correctly and can verify the backup step, the copy job, and the restore step all work and succeed. This is good.
The problem is it only succeeds when I have the restore step set to No Recovery Mode. When I change it to standby mode and check Disconnect users in the database... I receive the following in the restore job log:
Date11/9/2012 12:50:29 PM
LogJob History (LSRestore_btdatabase_DBNAME)
Step ID1
ServerSQL2008R2
Job NameLSRestore_SQL2005_DBNAME
Step NameLog shipping restore log job step.
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
2012-11-09 12:50:30.38*** Error: Could not apply log backup file 'd:\TLOGSHIPTEMP\DBNAME\DBNAME_20121109160000.trn' to secondary database 'DBNAME'.(Microsoft.SqlServer.Management.LogShipping) ***
2012-11-09 12:50:30.38*** Error: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2012-11-09 12:50:30.45*** Error: The log backup file 'd:\TLOGSHIPTEMP\DBNAME\DBNAME_20121109160000.trn' was verified but could not be applied to secondary database 'DBNAME'.(Microsoft.SqlServer.Management.LogShipping) ***
Has anyone out there successfully done this? Am I maybe missing something simple?
Thanks!
-Scott V
November 9, 2012 at 12:06 pm
sdvandeslunt (11/9/2012)
Hi guys, I have an issue I hope you can help with. I'm hoping I don't know the answer already...I have a SQL 2005 database (SP3) and a SQL 2008 R2 database. I'm trying to setup database log shipping from SQL2005.DBNAME to SQL2008R2.DBNAME and I have it all working correctly and can verify the backup step, the copy job, and the restore step all work and succeed. This is good.
The problem is it only succeeds when I have the restore step set to No Recovery Mode. When I change it to standby mode and check Disconnect users in the database... I receive the following in the restore job log:
Date11/9/2012 12:50:29 PM
LogJob History (LSRestore_btdatabase_DBNAME)
Step ID1
ServerSQL2008R2
Job NameLSRestore_SQL2005_DBNAME
Step NameLog shipping restore log job step.
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
2012-11-09 12:50:30.38*** Error: Could not apply log backup file 'd:\TLOGSHIPTEMP\DBNAME\DBNAME_20121109160000.trn' to secondary database 'DBNAME'.(Microsoft.SqlServer.Management.LogShipping) ***
2012-11-09 12:50:30.38*** Error: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2012-11-09 12:50:30.45*** Error: The log backup file 'd:\TLOGSHIPTEMP\DBNAME\DBNAME_20121109160000.trn' was verified but could not be applied to secondary database 'DBNAME'.(Microsoft.SqlServer.Management.LogShipping) ***
Has anyone out there successfully done this? Am I maybe missing something simple?
Thanks!
-Scott V
Standby actually brings the database online and makes it read only. That requires both database versions to be the same, so you cannot restore a 2005 database to a 2008 server in standby mode.
November 9, 2012 at 2:08 pm
Thanks for the reply. Unfortunately that answer is exactly what I was afraid was the truth. (When you say version you mean SQL server version right, not database compatibility level?)
:crying:
So now I guess I write a fancy program to restore a nightly backup and bring it to read-only every night. Maybe I'll do it via SSIS.
Hey, just curious, is there a T-SQL command to change the DB that is in NORECOVERY mode to be in READ-ONLY mode and then back to NORECOVERY mode. I would assume this would be possible in theory as there should be no transactions to worry about occuring/rolling back. The only command I know now would be to restore the final TLOG "with recovery" and then "Alter database set read_only"... but in between it would do some altering of the DB and make my ability to restore TLOGS null and void.
The full restore option wouldn't be so bad but this particular DB is over 100GB and I don't look forward to the prospect of copying that over the network every night...
Any other ideas out there?
Thanks,
-Scott V
November 9, 2012 at 3:58 pm
sdvandeslunt (11/9/2012)
Thanks for the reply. Unfortunately that answer is exactly what I was afraid was the truth. (When you say version you mean SQL server version right, not database compatibility level?):crying:
So now I guess I write a fancy program to restore a nightly backup and bring it to read-only every night. Maybe I'll do it via SSIS.
Hey, just curious, is there a T-SQL command to change the DB that is in NORECOVERY mode to be in READ-ONLY mode and then back to NORECOVERY mode. I would assume this would be possible in theory as there should be no transactions to worry about occuring/rolling back. The only command I know now would be to restore the final TLOG "with recovery" and then "Alter database set read_only"... but in between it would do some altering of the DB and make my ability to restore TLOGS null and void.
The full restore option wouldn't be so bad but this particular DB is over 100GB and I don't look forward to the prospect of copying that over the network every night...
Any other ideas out there?
Thanks,
-Scott V
we currently copy approx 100 gig database (give or take) over a 100meg pipe. what we do is zip the .bak file to save time on the transfer. if you get the winzip command line tools every thing can be done through SQL Agent (tsql and commandexec steps, dont even need xp_cmdshell). our zips take the actual size of the file down to approx 30 gig so its not to bad. takes just over 2 hours or so to backup zip and transfer. Test that time out as its a rough estimate and yours may be faster or slower. unzip and restore takes about an hour. if you have the time in your maint window it is an option.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 9, 2012 at 6:03 pm
Good call. Haven't used the commandexec for that but sounds like a good plan. It is only short term anyway. Thanks for the tip. 😀
-Scott
November 9, 2012 at 6:24 pm
George M Parker (11/9/2012)
Standby actually brings the database online and makes it read only.
Not quite. The DB is not brought online, if it was, further log backups would not be restorable. When a log is restored IN STANDBY both phases of recovery is run and information necessary to undo the undo are stored in the standby file.
As part of that, if the DB versions differ the upgrade steps would have to be run, and those as we well know are not undoable, hence this is not an option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2012 at 6:27 pm
sdvandeslunt (11/9/2012)
Hey, just curious, is there a T-SQL command to change the DB that is in NORECOVERY mode to be in READ-ONLY mode and then back to NORECOVERY mode.
Yes, but it won't allow you to then restore further log backups. In short, because the LSNs will no longer match as the ALTER DATABASE itself is logged and will fork the recovery at that point, not permitting any further log backups from being restored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2012 at 8:00 am
GilaMonster (11/9/2012)
George M Parker (11/9/2012)
Standby actually brings the database online and makes it read only.Not quite. The DB is not brought online, if it was, further log backups would not be restorable. When a log is restored IN STANDBY both phases of recovery is run and information necessary to undo the undo are stored in the standby file.
As part of that, if the DB versions differ the upgrade steps would have to be run, and those as we well know are not undoable, hence this is not an option.
Thanks Gail. "online was a poor choice of words"...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply