3

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...!

Improve this question
2
  • 1
    I really doubt the auto shrink setting is the cause. Are your full backups using WITH INIT? What other settings do they have enabled? What exact CHECKDB command did you run? What is on page 18469 (DBCC TRACEON(3604);DBCC PAGE('DatabaseName',1,18469,3);?
    – Aaron Bertrand
    Apr 8, 2014 at 16:15
  • I ran dbcc checkdb with EXTENDED_LOGICAL_CHECKS. I have the results of the DBCC PAGE command but it is large and won't fit in a comment - I'll have to add it as an edit to the question.
    – paulH
    Apr 8, 2014 at 16:47

1 Answer 1

Reset to default
1

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.