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?
Msg 3201, Level 16, State 2, Line 1 Cannot open backup device '\serverX1d$trlogs' Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally1
It means the service account of SQL Server (which used to start SQL Server service) does not have access to the source.
- START > RUN > type SERVICES.MSC.
- Find SQL Server Service > Right click and go to PROPERTIES.
- 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. dbasqlserviceact) and grant the account (ex. dbasqlserviceact) read & write privilege on the shared folder (network path).
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
Verify your differential has Differential Base LSN same as FirstLSN of Full Backup, using below query.
RESTORE HEADERONLY FROM DISK='\PATHFull_Backup.BAK' RESTORE HEADERONLY FROM DISK='\PATHDiff_Backup.BAK'
If it’s same, then First Restore Full backup with NORECOVERY.
RESTORE DATABASE DatabaseName FROM DISK = '\PATHBackup_File_Full.bak' WITH NORECOVERY;
Then Restore Differential correspond to the full backup you restored with NORECOVERY
RESTORE DATABASE DatabaseName FROM DISK = '\PATHBackup_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
To Restore Transaction Log Backup
RESTORE LOG DatabaseName FROM DISK = '\PathBackup_File_TLog.bak' WITH NORECOVERY; --If it's the last backup you need to restore use `WITH RECOVERY`
And your problem will be resolved.
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'SOFTWAREMicrosoftMSSQLServerMSSQLServer', N'BackupDirectory'
-Ensure the account you are using for restore have all needed acces on this folder :
-Restore your transaction log using Rajesh advices.