Backup Check options

[AGlennDa]

Scenario:
A script is performing backups and placing them in a primary location. If it fails to place the backup in the primary location, it will try to place it in a secondary location.

I would like to have DBA Dash tell me, if this happens, because if it does, I will then manually move the files backup to the primary location when i am able to do so.

The only way I can imagine for this to work, is by creating a custom check. I am placing errors in the CommandLog-table, so this would be the place to query the results for the custom check.
But my concern is that the custom check must be a SP placed in all the monitored databases? If I understand it correctly?

[DavidWiseman]

The custom check would need to be deployed to all the monitored instances - or at least the ones where you need this check to run. A custom check isn't the only option - you could also create an agent job that will fail when it requires your attention (which DBA Dash reports on anyway). Both options would work, but both require you to deploy code to your monitored instances. If you have a number of SQL instances, Invoke-DbaQuery from dbatools might help with the deployment.

Can you write your backup directly to the primary location? Why does it fail? If your restore process can handle multiple paths this might eliminate the need for moving the files. The log shipping tool I created can work with multiple paths and it can be used just as a database restore tool.

[AGlennDa]

My concern was that it needed to be located on any database, but in reality, it could be located in the master database of each instance being monitored, correct?
The agent job that fails could also be interesting to do.
The difference between the primary versus the secondary location is that one is local, and one is remote.
So, in a case where the connection fails at the time the backup is scheduled to run, and the it is not able to offload the backup to the remote located (primary), it is able to place it locally. And this is fine, but we need a way to get notified if this happens, so that we can manually move the backup afterwards, when the connection is re-established.

[DavidWiseman]

The procedure needs to be located on whatever database the service connects to - which is very likely the master database. You can deploy the procedure there.
To change the database the service connects to, set the initial catalog in the connection string. If it's not set it should connect to the master database unless the service account user's default database has been changed.