0

I have given access permission(sqlserveragent and mine) to the secondary folder but still I am getting this error. sql server : Ntservice\msssqlserver sql agent: domain\test

Message 2016-04-21 12:00:31.48 Starting transaction log copy. Secondary ID: 'bfa5d1ac-8c4c-4004-a1d1-27ab9a0bd6b4' 2016-04-21 12:00:31.49 Retrieving copy settings. Secondary ID: 'bfa5d1ac-8c4c-4004-a1d1-27ab9a0bd6b4' 2016-04-21 12:00:31.49 Retrieved copy settings. Primary Server: 'xxx', Primary Database: 'ac5T', Backup Source Directory: '\xx\Primaryshared', Backup Destination Directory: '\test\SecondaryShared', Last Copied File: '' 2016-04-21 12:00:31.51 Copying log backup files. Primary Server: 'CRYSTAL', Primary Database: 'ac5T', Backup Source Directory: '\xxx\Primaryshared', Backup Destination Directory: '\TEST\SecondaryShared' 2016-04-21 12:00:31.54 Copying log backup file to temporary work file. Source: '\xxx\Primaryshared\ac5T_20160421164404.trn', Destination: '\TEST\SecondaryShared\ac5T_20160421164404.wrk' 2016-04-21 12:00:31.56 *** Error: Access to the path '\TEST\SecondaryShared\ac5T_20160421164404.wrk' is denied.(mscorlib)

Taryn
  • 242,637
  • 56
  • 362
  • 405
surya teja
  • 143
  • 1
  • 2
  • 9
  • Shouldn't the path begin with double back slash? \\TEST\SecondaryShared – Ricardo C Apr 21 '16 at 19:33
  • So you are using a domain account domain\test ? try giving this account local admin to the host where '\TEST\SecondaryShared lives, and test. Permissions are tricky business. ACLs can be messed up too. sugested reading https://technet.microsoft.com/en-us/magazine/2007.07.securitywatch.aspx – Ricardo C Apr 21 '16 at 19:38
  • unsure what you mean. make the sql server agen run on an AD account like "Adventureworks\myServiceAccount". otherwise you have to give permissions to the folder to the entire machine, by machine name. And is also harder to troubleshoot – Ricardo C Apr 21 '16 at 19:55
  • test is the server name so I have included double slash, I have given the permissions to domain\test account also. Is there any issue with service account of the server we are running under NTService – surya teja Apr 21 '16 at 19:57
  • please go to SQL Server Configuration Manager and provide here exactly the account for SqlServer Agent. If it is NTServcie is NOT a domain account. – Ricardo C Apr 21 '16 at 20:05
  • Network Service Account The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format \$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE. https://msdn.microsoft.com/en-us/library/ms143504.aspx#Network_Service – Ricardo C Apr 21 '16 at 20:05
  • Thanks Ricardo. all the jobs are successful but the restore job is skipping the log backup file and it is showing a message that could not find a log backup file that could applied to the secondary database – surya teja Apr 22 '16 at 14:51
  • ummm no. that error message is telling you that the Agent cannot copy the file to begin with. at least the error message that you are showing you. but maybe we dont have all the info in here – Ricardo C Apr 22 '16 at 15:00
  • skipped log backup file. Secondary DB: '5T', File: 'F:\SecondaryShared\5T_20160422144001.trn' 2016-04-22 09:48:01.97 Could not find a log backup file that could be applied to secondary database '5T'. 2016-04-22 09:48:01.97 The restore operation was successful. Secondary Database: '5T', Number of log backup files restored: 0 2016-04-22 09:48:01.98 Deleting old log backup files. Primary Database: '5T' 2016-04-22 09:48:01.98 The restore operation was successful. Secondary ID: '603bba1a-f150-4acf-a994-da5763fc0436' – surya teja Apr 22 '16 at 15:20
  • sorry but i dont think we can help you further without the whole story/info. All I can tell you is that you need to fix file and folder permissions. and follow first advice: Test with an admin account. A real AD account, mind you. – Ricardo C Apr 22 '16 at 22:44

0 Answers0