Sql-server – Filestream database backup error

backupfilestreamsql-server-2008

We have DatabaseA that has filestream files in it. We then have an archive process which first deletes files in DatabaseB (different server) and then inserts new files from DatabaseA into DatabaseB via SSIS.

Each night we try to backup the database and we get the following error:

The operating system returned the error '1392 (The file or directory is corrupted and unreadable.)' while attempting 'FindNextFile' on \foldername guid.
[SQLSTATE 42000] (Error 3634)
BACKUP DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

When I look at the file path and folder name, the name is not the same as the folder name guid reported in the error. When we reorganize the index on the filestream guid column, we are able to backup the database. How do we recover from this corruption? DBCC checkdb? Is there something else we should be doing with filestream enabled database backups or the way that we are deleting the rows with the filestream column?

We are using native SQL Server

Backup command:

BACKUP DATABASE
to disk = '\share\backupshare\databasename\databasename.bak'
with noformat, init,
name='databasename-full database backup',
skip, norewind, nounload, compression, stats 10, checksum

Best Answer

FindNextFile is a Windows API call, so what's happening is the backup process is enumerating all the files in the FILESTREAM folder and backing them up. The error message indicates that the operating system cannot read the file system; this indicates, at least to me, that the file/folder structure on disk is corrupt. There would be a different error returned if the file/folder was simply not found. This may mean the I/O subsystem has problems. I would run chkdsk in Windows on that volume.

From the DBCC CHECKDB documentation, it doesn't look like FILESTREAM checks go beyond ensuring there is linkage between the database and the file system. Running chkdsk has the ability to do a low-level check of the disk contents itself, which is probably what's needed in this scenario. It certainly wouldn't hurt to run CHECKDB on the database anyway (read: regularly) either.

Related Question