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:

  1. Take a compressed backup of the corrupt database from production (SQL Server 2008R2), copy down to our (local) development server (SQL Server 2016) and run the repair (there isn't enough space on the production system to have two copies of the corrupt db attached concurrently).
  2. Restore two copies of this corrupt backup onto the development server where we do have enough space.
  3. Run the repair down on the development server with the allow_data_loss option for one of these dbs, rename to "DbNameHereRepaired" (most of the corrupt pages that I looked at with DBCC PAGE appeared to have had their data all set to 0x00 anyway).
  4. 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 disk=@tranLogBackupPath 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
        page=@corruptedPages
        from disk=N'D:\Restore\DbNameHereRepaired.bak'
        with norecovery;
    
        -- restore log over db
        restore log DbNameHereCorrupt from disk=@tranLogBackupPath
        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?

migrated from stackoverflow.com Dec 6 at 14:42

This question came from our site for professional and enthusiast programmers.

  • How many corrupt pages do you have? If you have more than 1000 pages, then the restore will fail. "The maximum number of pages that can be restored into any single file in a restore sequence is 1000. However, if you have more than a small number of damaged pages in a file, consider restoring the whole file instead of the pages." RESTORE Statements - Arguments (Transact-SQL). And some... – hot2use Dec 6 at 15:00
  • 103 pages, 676 consistency errors. I've managed to run the restore of the copy and the repair with allow data loss on our development server and recheck with dbcc checkdb afterwards - it appeared to work. – DB-GB Dec 6 at 15:06
  • @TomV Yes, I could tell by the object_ids & page_ids in DBCC CHECK, pretty much all of it is largely unused by any application. There's about 20-30 pages of data that could be of some value, fortunately we can re-process data from the raw source that it came from to recover it. – DB-GB Dec 6 at 15:22
  • 1
    In step #1, did you made copy_only full backup? – Andrey Nikolov Dec 6 at 15:23
  • @AndreyNikolov On the one that I'm using in the script above to try and get the "corrupted" test db on the development server to be in full recovery mode, no that was a standard backup. For the original backup that I used to copy down from production before that, yes that was copy_only. – DB-GB Dec 6 at 15:26

Your command for full backup

--Take full backup to begin new TLogChain

backup database DbNameHereCorrupt to disk = @fullBackupPath with init, differential;

does not make full backup.

DIFFERENTIAL

Used only with BACKUP DATABASE, specifies that the database or file backup should consist only of the portions of the database or file changed since the last full backup.

As @dbamex mentioned full and restore commands use different file names.

  • Thanks mija! I tried without the differential keyword (it was only added for speed, a full backup takes 3-4 hours). Yes, that was sort of the intention, but from the sound of what @dbamex has said, that's also the source of my problem :(. – DB-GB Dec 7 at 12:19

You're jumping ahead to solutions without identifying the problem first. What is the index id of the corrupt pages? If the index id is not 0 or 1, then these are non-clustered indexes - script them, drop them and recreate them and this should fix the corruption.

If they are index id 1 (clustered index) or 0 (heap), then you need to look at whether you have covering indexes using your NCIs to produce a copy of the data, then you can drop and recreate the table or the clustered index to fix corruption.

If you post the output from DBCC CHECKDB that might help identify possible solutions other than page-level restore.

  • Almost all of it is -1, 0 or 1 unfortunately, I managed to get away with just rebuilding indexes last time, but it would seem I'm not so lucky this time. Only one of the tables has covering indexes, and the covering index doesn't cover all columns in the table unfortunately. From the sounds of the comments above, it looks like a page-level restore (in the way that I'm trying to do it anyway) may not be possible without a lot of luck and finding a way to fudge LSNs. – DB-GB Dec 7 at 12:22

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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