Log Shipping Databases and Permissions Problems

Published On: 2014-01-29By:

Recently I was doing log shipping for a client in preparation to move their databases from one data center to another, when I was asked to change the drive that one of the target databases was being restored to.

No problem, I’ll just use ALTER DATABASE … MODIFY FILE (name=’xxx’, filename=’yyy’) to change the files, stop SQL, move the database files, and restart no problem.

Well apparently when doing this I managed to not reset the permissions correctly but only on the ndf file. Because SQL was able to access the MDF the database came up on NORECOVERY waiting for more logs to be restored. But when the next transaction log file was attempting to be restored, the error message that I got was not all that helpful to the problem at hand.

Msg 4319, Level 16, State 5, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘xxxxxxx’. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I tried restoring the older log backup and that didn’t help any. So I went and looked at the permissions again, and apparently SQL didn’t have write access to the NDF file so it wasn’t able to roll the logs forward. Thankfully I noticed the problem right away when I manually did the next log restore otherwise this could have been a nightmare to track down given that oh so helpful error message.

Denny


Contact the Author | Contact DCAC

One response to “Log Shipping Databases and Permissions Problems”

  1. mvpmattias says:

    Making sure the Service accounts share a Windows Domain Group, and that the Group has file- and folderpermissions on the Data-and logfilefolder solves this issue. And Windows Authentication solves the “Login/User/-problem.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link