Error join database to SQL availability group

Question

Can anyone help me with the following problem? I get the following error when I try to join a database to an availability group

"Restoring the database resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

---------------------------------------------- ADDITIONAL INFORMATION

Restore failed for Server '[computername]\SQL01'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)@EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

---

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation on'M:\UserLogs\OperationsManager.ldf'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&LinkId=20476

---

Background

• 2 Instances of SQL Server on two servers (Server1\instance1, Server1\instance2, Server2\instance1, Server2\instance2)
• SCCM databases on instance 1; SCOM databases on instance 2
• Moved SCCM databases to another server
• Changed collation on instance 1 on each server to the one used by the SCOM databases; restarted each server after doing this
• Detached OperationsManager database from instance 2, copied to instance 1 drives and attached to instance 1
• Created SQL Availability group on instance 1 for OperationsManager database
• Getting error message listed above when trying to add OperationsManager database to availability group
• Have checked permissions on instance 1 folders on both servers, also on share folder used to synch AG; have granted SQL Server instance, SQL Server service accounts (domain user accounts) full access to instance 1 folders and synch folder
• Have also made sure that SPNs for instance 1 on both servers are present & correct

• anon: Have you tried manually preparing the DBs to join the AG, by taking a full backup, plus at least one t-log backup, and restoring it to the secondary with norecovery? Do you get any errors when you step through manually? learn.microsoft.com/en-us/sql/database-engine/…

Answer 1

It seems like you don't have permission to the folder M:\UserLogs\.

Try the below method, it may be help you.

1. Grant full access to SQL server service account for M:\UserLogs. (Which must be a domain account)

2. Manually restore the database.

3. While adding the db to AG choose JOIN ONLY.