January 15, 2018 at 1:49 pm
Hi All,
I'm getting a page checksum failure when doing a full backup on a DB. But, when I run DBCC CHECKDB it reports no errors.
I've tried backing up to different locations (both local drive and network dirive) and the error still occurs.
This is a SQL 2014 instance.
These work with no errors
BACKUP DATABASE [myDB] TO DISK = N'c:\temp\myDB.bak'
BACKUP DATABASE [myDB] TO DISK = N'c:\temp\myDB.bak' WITH COMPRESSION
This fails
BACKUP DATABASE [myDB] TO DISK = N'c:\temp\myDB.bak' WITH CHECKSUM
Msg 3043, Level 16, State 1, Line 3
BACKUP 'myDB' detected an error on page (1:27990) in file 'E:\SQLData\myDB.mdf'.
This reports no errors:
DBCC CHECKDB (myDB) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS
What's interesting is that WITH COMPRESSION implies CHECKSUM, yet it doesn't throw an error.
Any thoughts?
Thanks
Peter
January 15, 2018 at 2:12 pm
This looks like it might just be some type of bug. Do you have the latest svc pack for that SQL applied?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2018 at 2:42 pm
Try to check corrupt data pages as you might be able o repair them using previous backup.
SELECT * FROM msdb..suspect_pages
January 15, 2018 at 3:49 pm
AZ Pete - Monday, January 15, 2018 1:49 PMWhat's interesting is that WITH COMPRESSION implies CHECKSUM, yet it doesn't throw an error.
Any thoughts?
Several posts indicate that Books Online is not correct in this respect.
When I've tested it myself on 2008 and specified with compression for the backups, there were no checksums for the databases according to the backupset table. I then tested restores on those using with checksum and got the error that the backupset contained no checksum information.
You can query backupset table in msdb to check the has_backup_checksums value
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply