First of all, thanks for having a look at this - We've got a large database in our production environment (1.26 TB), it's got a few hundred corrupt pages in it and has done for months, so the same corruption is in all of the backups available.
I got dragged into this late last week as it seems the scheduled jobs for reorganizing indexes have been failing for some time due to the corruption and we are now at the stage where the indexes on the largest and most commonly used tables range between 50% and 80% fragmentation which is seriously degrading application performance.
I've entertained a number of ideas on how I could remedy this situation (believe me, I'm more than open to alternatives) and from what I've read I think the following sounds like a good idea:
Rename other copy to DbNameHereCorrupt, attempt to run the page level restore using the following code:
alter database DbNameHereCorrupt set single_user with rollback immediate --set db to FULL recovery mode alter database DbNameHereCorrupt set recovery full --Declare paths for backups declare @fullBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-FullBackup.bck' declare @tranLogBackupPath nvarchar(max) = N'D:\Restore\DbNameHereCorrupt-LogBackup.bck' --Take full backup to begin new TLogChain backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential; --Immediately after whilst in single user mode, begin the t-log chain, this will also put the db in a restoring state backup log DbNameHereCorrupt to [email protected] with init, norecovery; --get corrupted pages declare @corruptedPages nvarchar(max) = ( select stuff ( ( select ',' + cast(s.file_id as nvarchar(20)) + ':' + cast(s.page_id as nvarchar(20)) from msdb.dbo.suspect_pages s where s.database_id = 20 for xml path('') ), 1 ,1, '' ) ) --push page-level restore restore database DbNameHereCorrupt [email protected] from disk=N'D:\Restore\DbNameHereRepaired.bak' with norecovery; -- restore log over db restore log DbNameHereCorrupt from [email protected] with norecovery; -- put db back into usable state restore database DbNameHereCorrupt with recovery --set db to SIMPLE recovery mode alter database DbNameHereCorrupt set recovery simple alter database DbNameHereCorrupt set multi_user
This appears to work how I think it's supposed to up until the page-level restore statement, where it errors out saying:
Msg 4346, Level 16, State 1, Line 35 RESTORE PAGE is not allowed with databases that use the simple recovery model or have broken the log backup chain.
This is my first time using the full recovery model, so I've probably done something wrong...I can see what looks like a small gap between the last lsn of the differential and the transaction backup, which I'm guessing that there shouldnt be.
Can anyone see what I've done wrong here? I've tried to follow examples on the web that all seem to follow a similar pattern, but I guess I'm missing something?