Recover records from ldf file.
in [Server Configuration]
|
From: Richard Mueller [MVP] on 19 Jan 2010 11:57 A customer suffered a hardware failure and managed to recover only the SQL Database *.ldf file from the failing hard drive. The *.mdf file was lost. The recovered *.ldf file is huge. The drive they were backing up to is full and the last backup is dated October. I believe that backups have been failing since October, which explains the large *.ldf file. I can restore the October backup, but am missing all activity since October. I believe the missing records are in the huge *.ldf file. Is there a way to recover these records from the *.ldf file? Can I view LSN numbers, for example? I only need rows added to one table. Can I script a dump of the rows? This is an SQL Server 2000 database. Thanks for any assistance. -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net --
From: Jay on 19 Jan 2010 14:00 Aside from reverse engineering it, your best bet would be to get a book on SQL Server 2000 internals (maybe browse through it at the bookstore). Once you have that, then you'll need a programming language that supports structures (like one of the C's, or maybe Perl) and figure it from there. Even assuming you get all that, you will have no guarantee that the data you recover will bring the database up to the current state and that you won't corrupt it somehow. Even if you do, this is going to be hard and take you quite a while. What if the log starts before the backup you recovered? How you gonna tell where to start? What if the log starts after the backup? How you going to deal with updates to records you don't have? Is your client willing to pay you for all this, with no guarantee? Is your client willing to wait for you do complete this? Are you willing to accept the liability of doing this, messing it up and having a mad client? "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in message news:uaR2CiSmKHA.1648(a)TK2MSFTNGP05.phx.gbl... >A customer suffered a hardware failure and managed to recover only the SQL >Database *.ldf file from the failing hard drive. The *.mdf file was lost. >The recovered *.ldf file is huge. The drive they were backing up to is full >and the last backup is dated October. I believe that backups have been >failing since October, which explains the large *.ldf file. I can restore >the October backup, but am missing all activity since October. > > I believe the missing records are in the huge *.ldf file. Is there a way > to recover these records from the *.ldf file? Can I view LSN numbers, for > example? I only need rows added to one table. Can I script a dump of the > rows? This is an SQL Server 2000 database. Thanks for any assistance. > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > >
From: B.Edwards on 19 Jan 2010 14:18 First thing I would try on the off-chance it might work is: 1. Backup the transaction log with the NO_TRUNCATE OPTION. If this is not possible you are hosed. 2. Restore from the most recent FULL database backup 3. Restore from the most recent DIFFERENTIAL backup (if one exists and it is more recent than the FULL backup in step 1) 3. Restore the Transaction log backup you made in step #1. -- do this to a separate test database. This will at least tell you if you even have data to restore and if the FULL database restore can even be used with the existing Log. Best case scenario--if the full database backup was made (October) and the transaction log backups have been failing every since, this might work. Also take at look at the following (particularly FILELISTONLY and HEADERONLY) to look at information (such as LSN's) on existing backups: RESTORE HEADERONLY RESTORE VERIFYONLY RESTORE FILELISTONLY You can buy third party software that will lets you look at the contents of the log file. But realistically, retrieving this and using it with the existing full backup would be messy at best and probably not work anyway. "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in message news:uaR2CiSmKHA.1648(a)TK2MSFTNGP05.phx.gbl... >A customer suffered a hardware failure and managed to recover only the SQL >Database *.ldf file from the failing hard drive. The *.mdf file was lost. >The recovered *.ldf file is huge. The drive they were backing up to is full >and the last backup is dated October. I believe that backups have been >failing since October, which explains the large *.ldf file. I can restore >the October backup, but am missing all activity since October. > > I believe the missing records are in the huge *.ldf file. Is there a way > to recover these records from the *.ldf file? Can I view LSN numbers, for > example? I only need rows added to one table. Can I script a dump of the > rows? This is an SQL Server 2000 database. Thanks for any assistance. > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > >
From: Richard Mueller [MVP] on 20 Jan 2010 00:00 I appreciate the advise. I use RESTORE HEADERONLY often, and am able to get LSN information from the good backup I have from last October. However, I failed to backup the ldf restored last week. I'm working with copies of everything so there is no danger I will make things worse with the production database. My goal is simply to access the records in the ldf file. If I can backup this transaction log, then restore it, I can dump out the rows of the table I need with a script to a csv file, then insert them into the production database with a script. I do similar work all the time. I restored my only good backup from October. This gives me an mdf and ldf file. I stopped the service and replaced the ldf file with the large ldf file recovered from the failed computer a week ago. I could start the service, but attempts to backup the log fail. The error is "Cannot associate files with different databases". The mdf file is for the same database as the ldf, just from different dates. Enterprise Manager shows the database as "Suspect" and shows nothing, no tables. I then replaced the mdf with that of a empty database, like we would give to a new customer. Same database name, just empty tables. Same result, I cannot backup the log. I also cannot backup the database. I can find no way to get the ldf to work with any mdf so I can recover the records I believe are there. Thanks anyway, unless you have other ideas. -- Richard Mueller MVP Directory Services Hilltop Lab - http://www.rlmueller.net -- "B.Edwards" <Bedwards(a)nospam.net> wrote in message news:ecdQzuTmKHA.1652(a)TK2MSFTNGP05.phx.gbl... > First thing I would try on the off-chance it might work is: > 1. Backup the transaction log with the NO_TRUNCATE OPTION. If this is > not possible you are hosed. > 2. Restore from the most recent FULL database backup > 3. Restore from the most recent DIFFERENTIAL backup (if one exists and > it is more recent than the FULL backup in step 1) > 3. Restore the Transaction log backup you made in step #1. > -- do this to a separate test database. > > This will at least tell you if you even have data to restore and if the > FULL database restore can even be used with the existing Log. Best case > scenario--if the full database backup was made (October) and the > transaction log backups have been failing every since, this might work. > > Also take at look at the following (particularly FILELISTONLY and > HEADERONLY) to look at information (such as LSN's) on existing backups: > > RESTORE HEADERONLY > RESTORE VERIFYONLY > RESTORE FILELISTONLY > > You can buy third party software that will lets you look at the contents > of the log file. But realistically, retrieving this and using it with the > existing full backup would be messy at best and probably not work anyway. > > "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in > message news:uaR2CiSmKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>A customer suffered a hardware failure and managed to recover only the SQL >>Database *.ldf file from the failing hard drive. The *.mdf file was lost. >>The recovered *.ldf file is huge. The drive they were backing up to is >>full and the last backup is dated October. I believe that backups have >>been failing since October, which explains the large *.ldf file. I can >>restore the October backup, but am missing all activity since October. >> >> I believe the missing records are in the huge *.ldf file. Is there a way >> to recover these records from the *.ldf file? Can I view LSN numbers, for >> example? I only need rows added to one table. Can I script a dump of the >> rows? This is an SQL Server 2000 database. Thanks for any assistance. >> >> -- >> Richard Mueller >> MVP Directory Services >> Hilltop Lab - http://www.rlmueller.net >> -- >> >> > >
From: Uri Dimant on 20 Jan 2010 02:55 Richard Perhaps it is time to develop the data recovery strategy on the client :-))) "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in message news:%23LUYH2YmKHA.2188(a)TK2MSFTNGP04.phx.gbl... >I appreciate the advise. I use RESTORE HEADERONLY often, and am able to get >LSN information from the good backup I have from last October. However, I >failed to backup the ldf restored last week. > > I'm working with copies of everything so there is no danger I will make > things worse with the production database. My goal is simply to access the > records in the ldf file. If I can backup this transaction log, then > restore it, I can dump out the rows of the table I need with a script to a > csv file, then insert them into the production database with a script. I > do similar work all the time. > > I restored my only good backup from October. This gives me an mdf and ldf > file. I stopped the service and replaced the ldf file with the large ldf > file recovered from the failed computer a week ago. I could start the > service, but attempts to backup the log fail. The error is "Cannot > associate files with different databases". The mdf file is for the same > database as the ldf, just from different dates. Enterprise Manager shows > the database as "Suspect" and shows nothing, no tables. > > I then replaced the mdf with that of a empty database, like we would give > to a new customer. Same database name, just empty tables. Same result, I > cannot backup the log. I also cannot backup the database. I can find no > way to get the ldf to work with any mdf so I can recover the records I > believe are there. Thanks anyway, unless you have other ideas. > > -- > Richard Mueller > MVP Directory Services > Hilltop Lab - http://www.rlmueller.net > -- > > "B.Edwards" <Bedwards(a)nospam.net> wrote in message > news:ecdQzuTmKHA.1652(a)TK2MSFTNGP05.phx.gbl... >> First thing I would try on the off-chance it might work is: >> 1. Backup the transaction log with the NO_TRUNCATE OPTION. If this is >> not possible you are hosed. >> 2. Restore from the most recent FULL database backup >> 3. Restore from the most recent DIFFERENTIAL backup (if one exists and >> it is more recent than the FULL backup in step 1) >> 3. Restore the Transaction log backup you made in step #1. >> -- do this to a separate test database. >> >> This will at least tell you if you even have data to restore and if the >> FULL database restore can even be used with the existing Log. Best case >> scenario--if the full database backup was made (October) and the >> transaction log backups have been failing every since, this might work. >> >> Also take at look at the following (particularly FILELISTONLY and >> HEADERONLY) to look at information (such as LSN's) on existing backups: >> >> RESTORE HEADERONLY >> RESTORE VERIFYONLY >> RESTORE FILELISTONLY >> >> You can buy third party software that will lets you look at the contents >> of the log file. But realistically, retrieving this and using it with >> the existing full backup would be messy at best and probably not work >> anyway. >> >> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote in >> message news:uaR2CiSmKHA.1648(a)TK2MSFTNGP05.phx.gbl... >>>A customer suffered a hardware failure and managed to recover only the >>>SQL Database *.ldf file from the failing hard drive. The *.mdf file was >>>lost. The recovered *.ldf file is huge. The drive they were backing up to >>>is full and the last backup is dated October. I believe that backups have >>>been failing since October, which explains the large *.ldf file. I can >>>restore the October backup, but am missing all activity since October. >>> >>> I believe the missing records are in the huge *.ldf file. Is there a way >>> to recover these records from the *.ldf file? Can I view LSN numbers, >>> for example? I only need rows added to one table. Can I script a dump of >>> the rows? This is an SQL Server 2000 database. Thanks for any >>> assistance. >>> >>> -- >>> Richard Mueller >>> MVP Directory Services >>> Hilltop Lab - http://www.rlmueller.net >>> -- >>> >>> >> >> > >
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: dfrgifc.exe & dfrgntfs.exe starting for some reason Next: Import data from Access to SQL |