SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Log Shipping failing when the primary DB is taken offline


Log Shipping failing when the primary DB is taken offline

Author
Message
Ravi-156610
Ravi-156610
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 333
We have Log Shipping setup on SQL 2008 instances and everything was running fine until the primary database was accidentally taken offline. We bought the DB online but noticed that the Log Shipping restore was failing. All the logs that were generated after the primary DB state change (offline\online) were not being restored on the secondary DB.
Does the database state change break the Log Shipping setup? How different would a database state change be from an instance restart?

Thanks,
Ravi
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87011 Visits: 45267
Ravi-156610 (12/6/2012)
We have Log Shipping setup on SQL 2008 instances and everything was running fine until the primary database was accidentally taken offline. We bought the DB online but noticed that the Log Shipping restore was failing.


With what error?

Does the database state change break the Log Shipping setup?


No.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19912 Visits: 17242
Ravi-156610 (12/6/2012)
We have Log Shipping setup on SQL 2008 instances and everything was running fine until the primary database was accidentally taken offline. We bought the DB online but noticed that the Log Shipping restore was failing. All the logs that were generated after the primary DB state change (offline\online) were not being restored on the secondary DB.
Does the database state change break the Log Shipping setup? How different would a database state change be from an instance restart?

Thanks,
Ravi

how long was log shipping down for?
Where the Log shipping jobs interrupted?
What does the history show for the copy and restore jobs on the secondary instance?

In most cases you can take a differential backup of the primary and apply to the secondary to resume log shipping. This will only be possible if there has been no full backup on the primary since the last successful secondary restore.

Check using this query against the primary and secondary to see if the differential base LSNs are synchronised

select DB_NAME(database_id), differential_base_lsn
from sys.master_files
where database_id = DB_ID('yourLSdb')
group by differential_base_lsn, database_id



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Ravi-156610
Ravi-156610
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 333
The LS_Restore job has been failing with this error:

"Error: The log backup file '\\ServerName\DBNAME_db\DBNAME_DB_20121203221511.trn' was verified but could not be applied to secondary database 'DNAME_DB'.(Microsoft.SqlServer.Management.LogShipping)"

"Error: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *
"


The primary DB state change occurred at around 10:00, local server time, and then the next TRN file from 10:15 local server time failed with the above errors.

Thanks
Ravi-156610
Ravi-156610
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 333
The Primary DB was offline for less than 1 minute.
There were no interruptions for the LS_Copy or LS_Restore jobs.

The primary DB state change occurred at around 10:00 local server time and the LS_Restore job for the next TRN file from 10:15 local server time failed with the below errors.

"Error: The log backup file '\\ServerName\DBNAME_db\DBNAME_DB_20121203221511.trn' was verified but could not be applied to secondary database 'DNAME_DB'.(Microsoft.SqlServer.Management.LogShipping)"

"Error: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) *
"


Thanks,
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19912 Visits: 17242
and the results of the query i supplied above????

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87011 Visits: 45267
Has the primary been restored from a backup?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19912 Visits: 17242
GilaMonster (12/6/2012)
Has the primary been restored from a backup?

Questions, questions. It's like the Spanish inquisition here lol :-D

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Ravi-156610
Ravi-156610
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 333
Gail,

No, the primary has not been restored from a backup.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19912 Visits: 17242
Perry Whittle (12/6/2012)
and the results of the query i supplied above????

?????????????? Whistling

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search