Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

---

Scenario:

During production scripts deployment, someone from requester who actually had DBO permission on the database somehow executed the scripts without taking valuable review of scripts from DBA and broken the DR server which was configured with log shipping disaster recovery strategy.

DBA monitoring alerts, notified us as priority 1 P1 ticket about this out of sync issue.

 

---

Workaround

While working on this issue, I first checked LS restore job history which were pointing something wrong happened with the Prod database data file.

Yes, an extra file .NDF file was got added on production database due to script deployment. I checked Prod database data file and script as well, Yes it got added though it was part of deployment but executed without DBA knowledge.

Below kind of error were captured in LS restore:

Could not apply log backup file .trn to secondary database Error:  Directory lookup for the file .ndf failed with the operating system error 3(The system cannot find the path specified.).Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement

---

This happened when there is mismatch of directory occurred, to resolve such issue identify the last successful applied transition log file from log shipping transaction report and use the immediate next transaction log backup file and RESTORE this with MOVE TO command as below.

RESTORE LOG [database]
FROM DISK='..._20210513061518.trn'
WITH MOVE 'NewFileLogical' to 'NewFilePhysical'
, norecovery,stats=1

Happy learning!

---

“A person who cannot decide his goal, simply cannot win.”

Chanakya