SQL error 3266 triggers when the backup device is detected by the SQL Server.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix SQL related issues.
In this context, we shall look into what causes this error and how to solve it.
Nature of SQL error message 3266?
Generally, when when the SQL Server detects filemark error on the backup device, this error is displayed. A more in-depth of the error message is shown below;
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.
All the information of backup device like the size of the block, the number of blocks in a device and son is contained in the filemark for a backup.
In the situation where a filemark error occurs, the SQL Server assumes that the whole backup media is corrupt. This will prevent writing any more data into the backup media.
What causes SQL error 3266?
Different factors result in this filemark error. Some of them are listed below;
i. A media failure on the device may occur where the backup is stored.
ii. If a write failure occurs during the creation of the backup.
iii. During the network backup procedure, if a connectivity loss occurs.
iv. Insufficient space. The database size is 2GB whereas a separate database is larger than 2 GB. So it is stored in an existing database.
v. Even though the filemark is available, the database is unable to read it. Or when the file mark itself is inaccessible.
How to fix "SQL error 3266"?
We can apply different approaches to fix this problem.
You can start by deleting the existing backup file.
Alternatively, you can use the "with format" option in the backup script.
To delete an existing backup file, simply run the following command to delete any old backup file;
xp_cmdshell ‘del \NetworkshareBackupFileName.bak’
From the above command, the "xp_cmdshell" command runs under the credentials of the SQL Server service account. After the backup file gets deleted, next time when an instance is started, the database must be backed up to the network share without any issue.