Few days back when I was checking the Backup Report, found that for one database backup did not happen for quite a lot of days. Asked my team member to check why it was failing. He found out that the Database Backup job was failing with the below error.
The backup data in '\NetworkSharebackupfilename.bak' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The backup script was supposed to overwrite the existing backup file since it was using with init. But it was not doing so. Within seconds the backup job would fail with the above error message.
What does this error message mean?
Error message 3266 is reported when SQL Server detects filemark error on the backup device. A filemark for a backup device holds all the details of a backup device like the size of the block, number of blocks in a device etc. If a filemark error is detected, SQL Server treats the entire backup media as corrupt and does not write any more data to the backup media.
How to resolve this issue?
Resolving this error message is quite easy. One option is to delete the existing backup file and the other one is to use the with format option in the backup script.
Since the first option was the easiest, we decided to delete the file and restart the backup job. But the DBA team did not have access to the network share where the backup file is located. But the SQL Server service account had FULL permissions on it. Hence the DBA executed the following query to delete the old backup file.
xp_cmdshell 'del \NetworkshareBackupFileName.bak'
Since the xp_cmdshell command executes under the credentials of the SQL Server service account, the backup file got deleted. When the job was executed the next time, the database was backed up to the network share successfully.
This is one of the reasons why I personally don’t recommend taking database backups directly on to a network share. I have seen the backup jobs reporting success, still the backup file was corrupt when it was needed most. The best approach is to take the database backup to local drive and then schedule a copy job using utilities like ROBOCOPY which provide far more flexibility while copying a file with options like auto retry on error.