lundi 23 février 2015

SQL Server 2008 R2, unable to restore, error 3456


I have a situation that is not easy to figure out, and thought I'd ask on this forum if others might have suggestions.


I'm running SQL Server 2008 R2 Standard SP3 on Windows Server 2008R2 Enterprise.


A database needed some maintenance, and after the fact I needed to restore on another server. I have a full db backup done with COPY_ONLY plus a set of 4 tlog backups.



  1. before starting, create tlogbackup1

  2. change from FULL to BULK_LOGGED recovery model

  3. add new filegroup

  4. add file to newfilegroup

  5. set newfilegroup to be default

  6. select into table (on newfilegroup)

  7. drop original table

  8. delete original file

  9. delete original filegroup

  10. change name of new table to match original table

  11. change file name of newfilegroup to match original filegroup

  12. change file name in catalog to match original file name

  13. change file name at OS level to match original file name

  14. set default filegroup to be the original

  15. bring db online

  16. change from BULK_LOGGED to FULL recovery model

  17. After all steps have completed, create tlogbackup2


The restore of all backups must use WITH MOVE, due to drive letter changes on the restore server.


Recovery steps:



RESTORE database SomeDB FROM DISK = 'D:\REPRO\SomeDB.bak'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup1.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1

RESTORE LOG SomeDB FROM DISK = 'D:\REPRO\tlogbackup2.trn'
WITH
MOVE 'SystemData' TO 'D:\SQLDATA\SomeDB.mdf'
,MOVE 'SystemDataPDS' TO 'D:\SqlData\SomeDB.ndf'
,MOVE 'SystemData_log' TO 'D:\SQLLogs\SomeDB.LDF'
,NORECOVERY
,stats = 1


The final tlog restore gets to 100% and then fails with error 3456:



Processed 368 pages for database 'SomeDB', file 'SystemData' on file 1.


Processed 7656520 pages for database 'SomeDB', file 'SystemDataPDS' on file 1.


Processed 172430 pages for database 'SomeDB', file 'SystemData_log' on file 1.


Msg 3456, Level 16, State 1, Line 1

Could not redo log record (210388:123648:232), for transaction ID (0:1016710921), on page (4:8088), database 'SomeDB' (database ID 6). Page: LSN = (0:0:1), type = 11. Log: OpCode = 4, context 11, PrevPageLSN: (210388:122007:1). Restore from a backup of the database, or repair the database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.



Just to verify that the full db backup was ok, I restored it ran CHECKDB, and there were no errors.


All feedback welcomed.


Thanks in advance,


Ned Otter





Aucun commentaire:

Enregistrer un commentaire