The restore of our database to new server hardware failed with a page error.
Message
SQL Server detected a logical consistency-based I/O error:
incorrect pageid (expected 49:8125916; actual 49:29097436).
It occurred during a read of page (49:8125916) in database ID 7 at
offset 0x00000f7fbb8000 in file x:\Databases\Data07\DataWarehouse_OperationalData_15.ndf'.
Additional messages in the SQL Server error log or system event
log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors;
for more information, see SQL Server Books Online.
A DBCC CheckDB will take us about a week on this 70 TB database and 6 years old server.
Is there any chance that it is my backup that is corrupt?
Or that the new server is at fault?
Or is it the production database on the old server that is the problem?
This is SQL Server 2016 SP1 CU1 and page_verify_option_desc is CHECKSUM.
This is what I can see from the error log on the new server:
Starting up database 'DataWarehouse'.
The database 'DataWarehouse' is marked RESTORING and is in a state that does not allow recovery to be run.
Error: 824, Severity: 16, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 49:8125916; actual 49:29097436). It occurred during a read of page (49:8125916) in database ID 7 at offset 0x00000f7fbb8000 in file 'S:\MSSQL\DSA\Databases\DataWarehouse\Data07\DataWarehouse_OperationalData_15.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Error: 18456, Severity: 14, State: 38.
When I run this on the old server:
DBCC TRACEON (3604);
DBCC PAGE ('datawarehouse', 49, 8125916, 0);
I get this
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
PAGE: (49:8125916)
BUFFER:
BUF @0x000000802C5E9300
bpage = 0x0000004DF03E8000 bhash = 0x0000000000000000 bpageno = (49:8125916)
bdbid = 5 breferences = 1 bcputicks = 0
bsampleCount = 0 bUse1 = 5844 bstat = 0x9
blog = 0x15ab215a bnext = 0x0000000000000000 bDirtyContext = 0x0000000000000000
bstat2 = 0x0
PAGE HEADER:
Page @0x0000004DF03E8000
m_pageId = (49:8125916) m_headerVersion = 1 m_type = 3
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0xa200
m_objId (AllocUnitId.idObj) = 10814647 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72058302786633728
Metadata: PartitionId = 72058370492596224 Metadata: IndexId = 1
Metadata: ObjectId = 1916450641 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 0 m_slotCnt = 2 m_freeCnt = 2452
m_freeData = 5764 m_reservedCnt = 0 m_lsn = (2455108:11140830:20)
m_xactReserved = 0 m_xdesId = (28:447547798) m_ghostRecCnt = 0
m_tornBits = 31453216 DB Frag ID = 1
Allocation Status
GAM (49:7668480) = ALLOCATED SGAM (49:7668481) = NOT ALLOCATED
PFS (49:8120352) = 0x42 ALLOCATED 80_PCT_FULL DIFF (49:7668486) = CHANGED
ML (49:7668487) = NOT MIN_LOGGED
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKFILEGROUP is currently running. Finished with out errors.
SeanGallardy commented on the binary numbers. It took me a long time to see what he meant, but I finally entered the two numbers in the Windows Calculator in Programmer mode, and here is what it shows:
Update:
We did another backup and restore from the old server to the new server.
This time we got this message:
Could not redo log record (2456609:4261461:64), for transaction ID (28:972770238), on page (48:211577379), allocation unit 72058359886184448, database 'DataWarehouse' (database ID 7).
Page: LSN = (2456609:3279166:236), allocation unit = 72058351460417536, type = 20. Log: OpCode = 2, context 5, PrevPageLSN: (2456609:4250688:2).
Restore from a backup of the database, or repair the database.
I have tried to Google this message, but the only advise I can find is to restore the database (which is what I'm trying to).
The real question is; what should I do next?
Best Answer
You do not have a backup until you did a restore.
This may not sound like the 'answer' to a DBA.SE question but, frankly, your only reasonable options here are to open a support case with Microsoft and ask for assistance, or hire a reputable professional (consultant or company) that has a proven record of fixing SQL Server corruption issues. If you can't afford these options, it means the data is not worth it.