I've been tasked to try to recover a database which suffered from corruption (due to I/O failure, which has been fixed since). I'm not familiar with the database or what it contains.
I've been given an old (~3 weeks) full backup and a series of transaction logs... however there are missing transaction logs, so I can only recover up to a certain date. There's like 2.5 weeks of data missing (and there's a lot of data being added to this database constantly).
I also have been given a copy of the corrupt database (which is accessible, but with a lot of pages corrupt/missing).
I've tried the typical
DBCC CHECKDB commands (still no
repair_allow_data_loss, that will be my last resort if nothing else works).
After many comes and goes to the database (the db is a 1.5 terabyte little monster and everything I do is slow and takes a while), I've tried to do an online page restore from the last known good backup for the corrupt pages.
To do that, I've done an script that creates many
RESTORE DATABASE <foo> PAGE='pages' FROM DISK='<bar.bak>' commands from the
DBCC CHECKDB output (básically a regex and a distinct)... so far so good, this worked up to a point where it said I had reached a limit of 1000 pages per file (there are 8 files on this db) per restore command.
So it asks me to "complete the online restore", but I'm at a loss at how to do that... I don't have a tail log or anything more complete than the full backup I'm starting with, so I basically don't know how to complete the restore to keep trying with the rest of pages.
I've tried a
RESTORE DATABASE <foo> WITH RECOVERY but that didn't work either, it asks me for a log which I don't have.
Does anyone have any tips on how I could try to recover anything from here? Or how to "complete" the online restore so I can keep trying to recover more pages? Would I have the same problem if I try an offline restore (basically adding
WITH NORECOVERY to everything and then try to bring it back at the end?)
Working out the database by hand is basically undoable... there are hundreds of tables with millions of rows and there's no clear meaning of what any of it are. The corrupt DB will fail on
SELECT queries after some million of rows but I'm not sure I can work out where. I've tried rebuilding all non-clustered indexes, but there are corrupt pages with row data, so that didn't work either.
Some data loss would be acceptable, but consistency on the DB should at least try to be achieved.
The corrupt database is -still- online and clients are working on it (so it keeps getting new data), so any process I do on the lab bench should be reproducible on the production database afterwards (downtime will be hard for it).
This is SQL Server 2014 Enterprise
PS: I'm no DBA... I'm a programmer, but the client has tried some "expert" sql disaster recovery services and they have given up, so I've been asked to look at it and see if I could do anything.
Update: after many tests, the page by page restoring was a no-go, so we've ditched the idea. We are going for a manual recovery (manually selecting missing records from the corrupt tables and inserting them in the last known good backup), doing some automated tools for it (again, there are hundreds and hundreds of tables).