December 22, 2003 at 7:57 am
What a fun way to start the week.
We had a I/O failure on Friday morning (1 of the RAID 5 disks failed), so we proceeded to shutdown the database and make a cold backup (copy of database filegroups) to another location.
For some reason the server showed that one of our databases was 'suspect' when restarting it. One of my fellow coworkers detached the database, proceeded to restore the backup of the datafiles and then tried to reatach the database.
All we get is: Error 3456 could not redo record (XXXX:XXXX:XXXX) for transaction ID (X:XXXXXXX) on page (X:XXXXX)......
Suggestion please
Thanks
Oscar
December 22, 2003 at 8:56 am
That is the error it shows when trying to attach?
Check the SQL log to see what it says before dettaching the db when it was in status suspected and please, post it here.
Do you have backups of the db up to the disk failure?
It seems to me SQL coudn't recovery some transactions that where in the Log file before dettaching.
December 22, 2003 at 9:22 am
quote:
One of my fellow coworkers detached the database, proceeded to restore the backup of the datafiles and then tried to reatach the database.
MDF and LDF files have to be restore as pair.
December 22, 2003 at 10:40 am
MDF, NDF & LDF files were copied and then we tried to reattach the database. It did not work.
I created a database with the exact filenames in my local machine, then shutdown it. After that I overwrote the MDF, NDF files (with the ones I backed up) and started the DB. I saw the database as 'Suspect'. I then changed the dabase into emergency mode and recreated the LOG file. Of course there were many consistency errors when I did a DBCC, but I could see data in the tables.
Because I havent found any reliable backups (or at least newer than a month ago) I am extracting the contents of the tables into a new database. I am still in this process as of right now. I will then dettach that new detabase, and attach it to the server. After that I will add indexes and constraints to each table.
A lot of work, but I think it will saves us this time.
Yes, backup strategy is something still missing here. I am new in both the company and sql server. I will need to RTFM and start making backups with transdactions logs.
I will notify how did it go.
Thanks
Oscar
December 22, 2003 at 2:44 pm
I was able to extract most of the tables.
I had problems with 5 of them, were they had the primary key clustered in a different filegroup (NDF). When I execute DBCC rebuild_log it just rebuilds the LOG based on the PRIMRY filegroup (MDF). So I am still researching on how to extract the tables whose data are NOT in the PRIMARY filegroup.
At least 85% of the database is now up and running with no data loss.
Any other suggestions are welcome.
Oscar
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply