Sql-server – MAXTRANSFERSIZE setting causes restore to fail

backupsql-server-2012

I have a database that seems to be functioning fine with no apparent errors except that any full backup taken is broken – attempting a restore fails with an error "RESTORE detected an error on page (18469:-1767164485)"

DBCC CheckDB on the database completes without errors.

EDIT #1

The backup is created with the following command:

BACKUP DATABASE @DatabaseName
TO
DISK = @backupFile1,
DISK = @backupFile2,
DISK = @backupFile3
WITH COMPRESSION, RETAINDAYS = 0, FORMAT, INIT, SKIP, NAME = @DatabaseName

EDIT #2

I just completed a backup using the GUI database backup in case my script was wrong somehow – This gave the same result of a damaged backup.

—

This only started happening last night. The only significant change that I am aware of is that I changed the 'auto-shrink' option of the database (it was originally set to true).

Is anybody aware of a problem in this area with SQL Server 2012 (specifically I'm on version 11.0.3128)? It could just be coincidence or related to something else entirely but that seems the most likely cause at the moment.

In addition does anybody have any advice on what to do in such a situation? The database is functioning fine (as far as I can determine), but I don't care to be without backups for very long…!

Best Answer

This turned out not to be an error with the backup, but with the restore command that I was using.

RESTORE verifyonly  FROM 
DISK = @backupFile
 WITH FILE=1,  STATS=5,  MAXTRANSFERSIZE=4194304

returned the error "Damage to the backup set was detected" some whereas

RESTORE verifyonly  FROM 
DISK = @backupFile
 WITH FILE=1,  STATS=5

returned "The backup set on file 1 is valid."

Which is ironic given that I was only using the MAXTRANSFERSIZE option to try to speed up the restore, but instead it cost me half a day!

The error only occurs on backups taken from last night onwards. Earlier backups verify fine whether I specify MAXTRANSFERSIZE or not, which is strange, but I guess I can live with not knowing the reason why that is unless somebody has an insight into that behaviour?

Related Solutions

Sql-server – Filestream database backup error

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.

Sql-server – SQL Server 2005 Restore Backup Status Recovery Pending

You can restore the database files to other locations using T-SQL commands to do the restore.

I did a test, something like:

CREATE DATABASE TestDB
ON (FILENAME='C:\test\TestDB.mdf', NAME='TestDB')
LOG ON (FILENAME='C:\test\TestDBLog.ldf', NAME='TestLog');
GO

BACKUP DATABASE TestDB TO DISK='C:\Test\TestDB.BAK';
GO

DROP DATABASE TestDB;
GO

RESTORE DATABASE TestDB FROM DISK='C:\Test\TestDB.BAK'
WITH MOVE 'TestDB' TO 'C:\SomeOtherFolder\TestDB.mdf'
    , MOVE 'TestLog' TO 'C:\SomeOtherFolder\TestLog.ldf'
    , RECOVERY;

This creates a database in one folder, takes a backup of the database, drops the database, then restores it from backup into a new folder. You should be able to modify the RESTORE DATABASE command to suit your needs.

Best Answer

Related Solutions

Sql-server – Filestream database backup error

Sql-server – SQL Server 2005 Restore Backup Status Recovery Pending

Related Question