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
One Response
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.