1

I have backed up transaction logs on my database and trying to restore them on another instance. But when trying to restore I receive an error: Operating system error 5(Access is denied.) and RESTORE LOG is terminating abnormally.

I guess, it means SQL Server user does not have permission for this operation, but I have already granted him public and sysadmin server roles. Could you please advise what could be the reason for this error?

  • Can you please post complete error, it's not complete error you have posted. – Rajesh Ranjan Sep 2 '16 at 8:29
  • I think the user that runs Sql Server Service does not have access to the transaction log files on the OS. – vercelli Sep 2 '16 at 8:36
  • My query for restore : restore log MyDB from disk = '\\serverX1\d$\trlogs' with norecovery – Nizami Khalilbayli Sep 2 '16 at 8:42
  • Complete error is : Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\\serverX1\d$\trlogs' Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally. – Nizami Khalilbayli Sep 2 '16 at 8:43
  • Yes, It looks like an OS permission problem. Have you tried to move the logs to the destination machine? – vercelli Sep 2 '16 at 8:51
2

First Error

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\\serverX1\d$\trlogs' Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally1

Solution: It means the service account of SQL Server (which used to start SQL Server service) does not have access to the source.

  1. START > RUN > type SERVICES.MSC.
  2. Find SQL Server Service > Right click and go to PROPERTIES.
  3. Go to LogOn/Security tab

If it's system account (NETWORK SERVICE, LOCAL SYSTEM etc.) then change it to any specific account (domain account like ex. dba\sqlserviceact) and grant the account (ex. dba\sqlserviceact) read & write privilege on the shared folder (network path).

Second Error

Msg 3117, Level 16, State 1, Line 1 The log or differential backup cannot be restored because no files are ready to rollforward. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally

**Solution: **

Verify your differential has Differential Base LSN same as FirstLSN of Full Backup, using below query.

RESTORE HEADERONLY FROM DISK='\\PATH\Full_Backup.BAK'

RESTORE HEADERONLY FROM DISK='\\PATH\Diff_Backup.BAK'

enter image description here

If it's same, then First Restore Full backup with NORECOVERY.

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Full.bak'
    WITH NORECOVERY;

Then Restore Differential correspond to the full backup you restored with NORECOVERY

    RESTORE DATABASE DatabaseName
    FROM DISK = '\\PATH\Backup_File_Diff.bak'
    WITH NORECOVERY; --Use WITH RECOVERY if you need to recover data for the point of time

Now if you have Transaction Log backup to restore. Restore all the transaction log backup with NORECOVERY. Only Last T-Log backup (To the point of time you want to recover) will be restored with RECOVERY

To Restore Transaction Log Backup

RESTORE LOG DatabaseName FROM DISK = '\\Path\Backup_File_TLog.bak' WITH NORECOVERY; --If it's the last backup you need to restore use `WITH RECOVERY`

And your problem will be resolved.

Thanks

  • 1
    Actually, restoring a Differential with RECOVERY will prevent you from restoring any transaction logs. ALL restore commands should specify NO RECOVERY except the very last one you execute. – Scott Hodgin Sep 2 '16 at 9:21
1

It is an OS security level access problem. I will try the followings :

-Copy your transaction log backup on the destination server, in the default location dedicated to backup. You can determine this location with

EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

-Ensure the account you are using for restore have all needed acces on this folder :

Folder security

-Restore your transaction log using Rajesh advices.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.