Whenever you are into a business of “fixing the issue” – you should always find in error message first. The whole approach of solving the issue changes if we go behind the wrong error message. Let us learn about the error Backup Failure.
Once of my client told that their backups are failing randomly. If you are a DBA, you would agree with me that there can be many different reasons of backup failure, mostly permission or space issue. So, I asked them to send me email with exact issue and the error message from a SQL Server ErrorLog file. So now, they reported that – Our backups are “randomly” failing to backup up a database to a file share will with below errors in ERRORLOG file.
2017-05-11 11:36:23.10 spid109 Error: 18204, Severity: 16, State: 1.
2017-05-11 11:36:23.10 spid109 BackupDiskFile::CreateMedia: Backup device ‘\\ozone-fs\Backups\DBName.bak’ failed to create. Operating system error 1240(The account is not authorized to log in from this station.).
2017-05-11 11:36:23.10 Backup Error: 3041, Severity: 16, State: 1.
2017-05-11 11:36:23.10 Backup BACKUP failed to complete the command BACKUP DATABASE DBName. Check the backup application log for detailed messages.
The first observation from error message is that it’s a share not a local drive. I was curious to know what kind of share it is? They informed that this is Scale-Out File Server (SOFS) configuration with CA (Continuously Available) shares. When I searched for the error message, it was talking about SMB and some registry level change, but had nothing to do with CA shares.
I had no idea about this feature so I did some research about this feature and found something interesting. Read the article here.
Based on my research and learning, for the SQL backups, we should not use Scale-Out File Server (SOFS) configuration with CA (Continuously Available) shares, in favor of General Use File Server Cluster. Here is the reference.
Reference: Pinal Dave (http://blog.SQLAuthority.com)