I was doing some random testing for a different log shipping related scenario, when I ran into a restore failure reported by the log shipping restore job. A snippet of the error message is shown below:
*** Error: Could not apply log backup file ‘<backup file name>’ to secondary database ‘<database name>’.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: The operation cannot be performed on a database with database snapshots or active DBCC replicas.
The above error is reported as Error #5094. The error message is shown in bold above. Now this error occurred because I had created a database snapshot on the log shopped secondary database which was operating in standby (warm standby) mode. In such an event, you can only perform a log shipping restore on the secondary server after the database snapshot is dropped.
This also applies to the active DBCC replicas as well. Which means a DBCC CHECK being executed on the standby database could also cause a log shipping restore to fail. This issue would never occur if the secondary database was in no recovery mode since a database snapshot cannot be created for this log shipping operating mode.
The Ring_Buffer_Exception reports the error as:
The idea of putting this blog post together was to ensure that you were aware of this behavior before you created database snapshots on your standby log shipped secondary databases for reporting purposes.