The idea of taking Backups of databases are a very important aspect in Server Management for Data Security and recovery after an unexpected event. Sometimes, a database backup job could fail resulting in SQL error Msg 3266.

Here at Ibmi Media, as part of our Server Management Services, we always help our Customers to monitor and fix SQL related errors.

In this context, we shall look into what causes this error and how best to fix it.

More about SQL error Msg 3266?

Sometimes Webmasters experience that their database backups job fails with an error Msg 3266 "BACKUP DATABASE is terminating abnormally". When this error occurs, you will see an error message such as this;

Msg 3266, Level 16, State 1, Line 1
The Microsoft Tape Format (MTF) soft filemark database on backup device 'devicename' cannot be read, inhibiting random access.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.

What this error signifies is that the filemark in the backup device could not be read.

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.

When a filemark error triggers, SQL Server treats the entire backup media as corrupt and does not write any more data to the backup media.

What triggers the filemark error?

The main reasons why this error occurs are outlined below;

i. A media failure on the device that stores the backup.

ii. A write failure during the creation of the backup.

iii. Connectivity issues and loss during the network backup procedure.

iv. Lack of disk space.

v. The database is not able to read the file mark or inaccessibility of the file mark itself.

[It is important to fix  SQL error Msg 3266 before it affects your database backups! Ensure the availability of database backups with the 24/7 monitoring & maintenance by our experienced server specialists. ]

How to fix SQL error Msg 3266?

To solve this error message we can apply the following options provided below;

i. Delete the existing backup file

ii. Use the format option in the backup script.

The easiest way to fix this error is to delete the existing backup file. To do this, follow the guideline below.

Start by manually deleting or erasing the device to allow SQL Server to perform new backups to the backup device. To do this, use the command below ;

BACKUP DATABASE mydatabase TO DISK='C:\MyDatabase.bak' with FORMAT

If the error message occurs during a restore operation, it may be possible to retrieve other backup sets from the device by specifying the file number.

To determine if multiple backup sets are on a device, run the following code from Query Analyzer:

RESTORE HEADERONLY FROM DISK='C:\MyDatabase.bak'

Each backup set has one entry in the output. To indicate a specific backup set, use this code;

RESTORE DATABASE mydatabase FROM DISK='C:\MyDatabase.bak WITH FILE = FileNumber

Here, FileNumber represents the backup set number we want to restore.

Need support in fixing SQL error Msg 3266? We are available to help you today.