Recover records from ldf file.
in [Server Configuration]
|
From: Jay on 20 Jan 2010 16:07 I will try your experiment later. You're saying that if I have a log backup with the 1st record predating the database (the transaction in the log has already been applied), then when the log restore is done, it will skip log records until it gets to the first one after the backup you restored? (man that phrasing sucks, but I hope you understand it) Also, does this still work if the 1st log record in the .ldf file is from after the good backup and not before? (my guess is no) As to the second server stuff, I just had it in my head that the original server was hosed, that's all. "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:un%23SOJhmKHA.1212(a)TK2MSFTNGP04.phx.gbl... > First of all, no need to get the ldf file to some other system. Try this > yourself. Create a db. Full recovery. Do db backup. Stop SQL Server. > Delete the ldf file. Start SQL server. Db is suspect. Do log backup using > NO_TRUNCATE. Restore db backup and then log backup. > So, what is this about the second server? In most cases, the customer > couldn't keep their hands off the system when I get to it. They tend to > have done the strangest things, like detach, creating a dummy mdf file and > whatever. My point is that all you need is the ldf file, you don't *need* > the original install. > You *do* need an unbroken chain of log backups, though. That doesn't > necessarily mean more then one ldf file. If the db was in full recovery > since you did the db backup, and no stupid things were does (like backup > log using truncate_only), then you can do this log backup and it will > contain all log records since before the db backup. > > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jay" <spam(a)nospam.org> wrote in message > news:OyMrUChmKHA.4872(a)TK2MSFTNGP05.phx.gbl... >> Thanks for the link Tibor, I was looking in the wrong forum. >> >> Now I'm having some trouble getting my head around this one. Let's see if >> I have what the situation correct? >> >> -- Last good backup Oct 1 >> -- Jan the .mdf's are toast >> -- Have a huge .ldf file, but we don't know when it started >> -- Get the .ldf file on a system with a (basically) blank .mdf file and >> do a NO_TRUNCATE log backup. >> -- Restore the Oct backup >> -- Restore the log backup you just created? >> >> How does the log backup you just created work as an unbroken chain from >> the Oct backup? After all, the datestamp on the 1st log record is >> unlikely to match up to the Oct backup. >> >> I don't understand how this can work. >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:e7D7PrgmKHA.256(a)TK2MSFTNGP05.phx.gbl... >>> Here goes: >>> http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/13402575-58f0-4985-9853-3e687372aba2 >>> >>> Hmm, it seems Richard did reply there, but I wasn't notified by email - >>> hence I didn't see hat reply. My apologies. >>> To repeat: when you "slide in" files from a different db (the crashed >>> database), you should only do the ldf file. SQL server should see no mdf >>> file. The situation should be as if you have a SQL Server, stop it, >>> delete the mdf file and then start the SQL server. Yes, the db will be >>> suspect, but you can do the log backup using NO_TRUNCATE. >>> If that doesn't work then either correct steps weren't followed (I >>> believe there's a KB on this - at least here used to be) or something >>> strange is going on. In any event, it is probably MS Support time... >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:#VRYrUgmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>> Tibor, I looked on the MSDN forums for the post you're referring to, >>>> but couldn't find it. Would you please be kind enough to provide the >>>> link? >>>> >>>> Thanks, >>>> Jay >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>> wrote in message news:Ou9caOfmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>> It seems you didn't follow my advice from the MDN forums. Delete >>>>> *both* files and slide in *only* the ldf file. Then make sure you do >>>>> the log backup using NO_TRUNCATE. See my reply in MSDN forum for >>>>> elaboration. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>> >>>>> >>>>> >>>>> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote >>>>> in message news:#LUYH2YmKHA.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 >>>>>>>> -- >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: Jay on 20 Jan 2010 16:17 On a second read, I'm confused on a second point. If the ldf file is hosed, but you have the mdf, aren't you ok to begin with (from a recovery point of view). You said: "Delete the ldf file" Wouldn't the experiment require and intact .ldf and a hosed .mdf? "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:un%23SOJhmKHA.1212(a)TK2MSFTNGP04.phx.gbl... > First of all, no need to get the ldf file to some other system. Try this > yourself. Create a db. Full recovery. Do db backup. Stop SQL Server. > Delete the ldf file. Start SQL server. Db is suspect. Do log backup using > NO_TRUNCATE. Restore db backup and then log backup. > So, what is this about the second server? In most cases, the customer > couldn't keep their hands off the system when I get to it. They tend to > have done the strangest things, like detach, creating a dummy mdf file and > whatever. My point is that all you need is the ldf file, you don't *need* > the original install. > You *do* need an unbroken chain of log backups, though. That doesn't > necessarily mean more then one ldf file. If the db was in full recovery > since you did the db backup, and no stupid things were does (like backup > log using truncate_only), then you can do this log backup and it will > contain all log records since before the db backup. > > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jay" <spam(a)nospam.org> wrote in message > news:OyMrUChmKHA.4872(a)TK2MSFTNGP05.phx.gbl... >> Thanks for the link Tibor, I was looking in the wrong forum. >> >> Now I'm having some trouble getting my head around this one. Let's see if >> I have what the situation correct? >> >> -- Last good backup Oct 1 >> -- Jan the .mdf's are toast >> -- Have a huge .ldf file, but we don't know when it started >> -- Get the .ldf file on a system with a (basically) blank .mdf file and >> do a NO_TRUNCATE log backup. >> -- Restore the Oct backup >> -- Restore the log backup you just created? >> >> How does the log backup you just created work as an unbroken chain from >> the Oct backup? After all, the datestamp on the 1st log record is >> unlikely to match up to the Oct backup. >> >> I don't understand how this can work. >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:e7D7PrgmKHA.256(a)TK2MSFTNGP05.phx.gbl... >>> Here goes: >>> http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/13402575-58f0-4985-9853-3e687372aba2 >>> >>> Hmm, it seems Richard did reply there, but I wasn't notified by email - >>> hence I didn't see hat reply. My apologies. >>> To repeat: when you "slide in" files from a different db (the crashed >>> database), you should only do the ldf file. SQL server should see no mdf >>> file. The situation should be as if you have a SQL Server, stop it, >>> delete the mdf file and then start the SQL server. Yes, the db will be >>> suspect, but you can do the log backup using NO_TRUNCATE. >>> If that doesn't work then either correct steps weren't followed (I >>> believe there's a KB on this - at least here used to be) or something >>> strange is going on. In any event, it is probably MS Support time... >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:#VRYrUgmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>> Tibor, I looked on the MSDN forums for the post you're referring to, >>>> but couldn't find it. Would you please be kind enough to provide the >>>> link? >>>> >>>> Thanks, >>>> Jay >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>> wrote in message news:Ou9caOfmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>> It seems you didn't follow my advice from the MDN forums. Delete >>>>> *both* files and slide in *only* the ldf file. Then make sure you do >>>>> the log backup using NO_TRUNCATE. See my reply in MSDN forum for >>>>> elaboration. >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>> >>>>> >>>>> >>>>> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote >>>>> in message news:#LUYH2YmKHA.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 >>>>>>>> -- >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: Tibor Karaszi on 20 Jan 2010 16:29 > You're saying that if I have a log backup with the 1st record predating > the database (the transaction in the log has already been applied), then > when the log restore is done, it will skip log records until it gets to > the first one after the backup you restored? Exactly. This is how every db backup/ first-log-backup-thereafter combo functions. > (man that phrasing sucks, but I hope you understand it) Didn't suck at all. You phrased it better than I would. > Also, does this still work if the 1st log record in the .ldf file is from > after the good backup and not before? (my guess is no) Nope. Can't have a hole i the log record chain... No exceptions. :-) > As to the second server stuff, I just had it in my head that the original > server was hosed, that's all. Yes, that is possible, I wasn't 100% percent certain of the exact circumstances for this particular case... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jay" <spam(a)nospam.org> wrote in message news:eciCUShmKHA.1652(a)TK2MSFTNGP05.phx.gbl... > I will try your experiment later. > > You're saying that if I have a log backup with the 1st record predating > the database (the transaction in the log has already been applied), then > when the log restore is done, it will skip log records until it gets to > the first one after the backup you restored? (man that phrasing sucks, but > I hope you understand it) > > Also, does this still work if the 1st log record in the .ldf file is from > after the good backup and not before? (my guess is no) > > As to the second server stuff, I just had it in my head that the original > server was hosed, that's all. > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:un%23SOJhmKHA.1212(a)TK2MSFTNGP04.phx.gbl... >> First of all, no need to get the ldf file to some other system. Try this >> yourself. Create a db. Full recovery. Do db backup. Stop SQL Server. >> Delete the ldf file. Start SQL server. Db is suspect. Do log backup using >> NO_TRUNCATE. Restore db backup and then log backup. >> So, what is this about the second server? In most cases, the customer >> couldn't keep their hands off the system when I get to it. They tend to >> have done the strangest things, like detach, creating a dummy mdf file >> and whatever. My point is that all you need is the ldf file, you don't >> *need* the original install. >> You *do* need an unbroken chain of log backups, though. That doesn't >> necessarily mean more then one ldf file. If the db was in full recovery >> since you did the db backup, and no stupid things were does (like backup >> log using truncate_only), then you can do this log backup and it will >> contain all log records since before the db backup. >> >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:OyMrUChmKHA.4872(a)TK2MSFTNGP05.phx.gbl... >>> Thanks for the link Tibor, I was looking in the wrong forum. >>> >>> Now I'm having some trouble getting my head around this one. Let's see >>> if I have what the situation correct? >>> >>> -- Last good backup Oct 1 >>> -- Jan the .mdf's are toast >>> -- Have a huge .ldf file, but we don't know when it started >>> -- Get the .ldf file on a system with a (basically) blank .mdf file and >>> do a NO_TRUNCATE log backup. >>> -- Restore the Oct backup >>> -- Restore the log backup you just created? >>> >>> How does the log backup you just created work as an unbroken chain from >>> the Oct backup? After all, the datestamp on the 1st log record is >>> unlikely to match up to the Oct backup. >>> >>> I don't understand how this can work. >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >>> in message news:e7D7PrgmKHA.256(a)TK2MSFTNGP05.phx.gbl... >>>> Here goes: >>>> http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/13402575-58f0-4985-9853-3e687372aba2 >>>> >>>> Hmm, it seems Richard did reply there, but I wasn't notified by email - >>>> hence I didn't see hat reply. My apologies. >>>> To repeat: when you "slide in" files from a different db (the crashed >>>> database), you should only do the ldf file. SQL server should see no >>>> mdf file. The situation should be as if you have a SQL Server, stop it, >>>> delete the mdf file and then start the SQL server. Yes, the db will be >>>> suspect, but you can do the log backup using NO_TRUNCATE. >>>> If that doesn't work then either correct steps weren't followed (I >>>> believe there's a KB on this - at least here used to be) or something >>>> strange is going on. In any event, it is probably MS Support time... >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> >>>> "Jay" <spam(a)nospam.org> wrote in message >>>> news:#VRYrUgmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>> Tibor, I looked on the MSDN forums for the post you're referring to, >>>>> but couldn't find it. Would you please be kind enough to provide the >>>>> link? >>>>> >>>>> Thanks, >>>>> Jay >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>>> wrote in message news:Ou9caOfmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>>> It seems you didn't follow my advice from the MDN forums. Delete >>>>>> *both* files and slide in *only* the ldf file. Then make sure you do >>>>>> the log backup using NO_TRUNCATE. See my reply in MSDN forum for >>>>>> elaboration. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>>> >>>>>> >>>>>> >>>>>> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote >>>>>> in message news:#LUYH2YmKHA.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 >>>>>>>>> -- >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > >
From: Jay on 20 Jan 2010 16:43 Well thanks Tibor, I think I learned something useful today. Hope I never need it though. Sorry I had to hijack the thread, but at least it stayed on topic. Last question. As of what version of SQL Server was this done? "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:O7iYGehmKHA.5840(a)TK2MSFTNGP05.phx.gbl... >> You're saying that if I have a log backup with the 1st record predating >> the database (the transaction in the log has already been applied), then >> when the log restore is done, it will skip log records until it gets to >> the first one after the backup you restored? > > Exactly. This is how every db backup/ first-log-backup-thereafter combo > functions. > > >> (man that phrasing sucks, but I hope you understand it) > > Didn't suck at all. You phrased it better than I would. > > >> Also, does this still work if the 1st log record in the .ldf file is from >> after the good backup and not before? (my guess is no) > > Nope. Can't have a hole i the log record chain... No exceptions. :-) > > >> As to the second server stuff, I just had it in my head that the original >> server was hosed, that's all. > > Yes, that is possible, I wasn't 100% percent certain of the exact > circumstances for this particular case... > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Jay" <spam(a)nospam.org> wrote in message > news:eciCUShmKHA.1652(a)TK2MSFTNGP05.phx.gbl... >> I will try your experiment later. >> >> You're saying that if I have a log backup with the 1st record predating >> the database (the transaction in the log has already been applied), then >> when the log restore is done, it will skip log records until it gets to >> the first one after the backup you restored? (man that phrasing sucks, >> but I hope you understand it) >> >> Also, does this still work if the 1st log record in the .ldf file is from >> after the good backup and not before? (my guess is no) >> >> As to the second server stuff, I just had it in my head that the original >> server was hosed, that's all. >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:un%23SOJhmKHA.1212(a)TK2MSFTNGP04.phx.gbl... >>> First of all, no need to get the ldf file to some other system. Try this >>> yourself. Create a db. Full recovery. Do db backup. Stop SQL Server. >>> Delete the ldf file. Start SQL server. Db is suspect. Do log backup >>> using NO_TRUNCATE. Restore db backup and then log backup. >>> So, what is this about the second server? In most cases, the customer >>> couldn't keep their hands off the system when I get to it. They tend to >>> have done the strangest things, like detach, creating a dummy mdf file >>> and whatever. My point is that all you need is the ldf file, you don't >>> *need* the original install. >>> You *do* need an unbroken chain of log backups, though. That doesn't >>> necessarily mean more then one ldf file. If the db was in full recovery >>> since you did the db backup, and no stupid things were does (like backup >>> log using truncate_only), then you can do this log backup and it will >>> contain all log records since before the db backup. >>> >>> >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:OyMrUChmKHA.4872(a)TK2MSFTNGP05.phx.gbl... >>>> Thanks for the link Tibor, I was looking in the wrong forum. >>>> >>>> Now I'm having some trouble getting my head around this one. Let's see >>>> if I have what the situation correct? >>>> >>>> -- Last good backup Oct 1 >>>> -- Jan the .mdf's are toast >>>> -- Have a huge .ldf file, but we don't know when it started >>>> -- Get the .ldf file on a system with a (basically) blank .mdf file and >>>> do a NO_TRUNCATE log backup. >>>> -- Restore the Oct backup >>>> -- Restore the log backup you just created? >>>> >>>> How does the log backup you just created work as an unbroken chain from >>>> the Oct backup? After all, the datestamp on the 1st log record is >>>> unlikely to match up to the Oct backup. >>>> >>>> I don't understand how this can work. >>>> >>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>> wrote in message news:e7D7PrgmKHA.256(a)TK2MSFTNGP05.phx.gbl... >>>>> Here goes: >>>>> http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/13402575-58f0-4985-9853-3e687372aba2 >>>>> >>>>> Hmm, it seems Richard did reply there, but I wasn't notified by >>>>> email - hence I didn't see hat reply. My apologies. >>>>> To repeat: when you "slide in" files from a different db (the crashed >>>>> database), you should only do the ldf file. SQL server should see no >>>>> mdf file. The situation should be as if you have a SQL Server, stop >>>>> it, delete the mdf file and then start the SQL server. Yes, the db >>>>> will be suspect, but you can do the log backup using NO_TRUNCATE. >>>>> If that doesn't work then either correct steps weren't followed (I >>>>> believe there's a KB on this - at least here used to be) or something >>>>> strange is going on. In any event, it is probably MS Support time... >>>>> >>>>> -- >>>>> Tibor Karaszi, SQL Server MVP >>>>> http://www.karaszi.com/sqlserver/default.asp >>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>> >>>>> >>>>> >>>>> "Jay" <spam(a)nospam.org> wrote in message >>>>> news:#VRYrUgmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>>> Tibor, I looked on the MSDN forums for the post you're referring to, >>>>>> but couldn't find it. Would you please be kind enough to provide the >>>>>> link? >>>>>> >>>>>> Thanks, >>>>>> Jay >>>>>> >>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>>>> wrote in message news:Ou9caOfmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>>>> It seems you didn't follow my advice from the MDN forums. Delete >>>>>>> *both* files and slide in *only* the ldf file. Then make sure you do >>>>>>> the log backup using NO_TRUNCATE. See my reply in MSDN forum for >>>>>>> elaboration. >>>>>>> >>>>>>> -- >>>>>>> Tibor Karaszi, SQL Server MVP >>>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>>>> >>>>>>> >>>>>>> >>>>>>> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> >>>>>>> wrote in message news:#LUYH2YmKHA.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 >>>>>>>>>> -- >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>>> >>>> >>>> >> >>
From: Tibor Karaszi on 20 Jan 2010 17:23 Sorry. That was meant to be "delete the mdf". -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jay" <spam(a)nospam.org> wrote in message news:uEDkyXhmKHA.2188(a)TK2MSFTNGP04.phx.gbl... > On a second read, I'm confused on a second point. If the ldf file is > hosed, but you have the mdf, aren't you ok to begin with (from a recovery > point of view). > > You said: "Delete the ldf file" > > Wouldn't the experiment require and intact .ldf and a hosed .mdf? > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:un%23SOJhmKHA.1212(a)TK2MSFTNGP04.phx.gbl... >> First of all, no need to get the ldf file to some other system. Try this >> yourself. Create a db. Full recovery. Do db backup. Stop SQL Server. >> Delete the ldf file. Start SQL server. Db is suspect. Do log backup using >> NO_TRUNCATE. Restore db backup and then log backup. >> So, what is this about the second server? In most cases, the customer >> couldn't keep their hands off the system when I get to it. They tend to >> have done the strangest things, like detach, creating a dummy mdf file >> and whatever. My point is that all you need is the ldf file, you don't >> *need* the original install. >> You *do* need an unbroken chain of log backups, though. That doesn't >> necessarily mean more then one ldf file. If the db was in full recovery >> since you did the db backup, and no stupid things were does (like backup >> log using truncate_only), then you can do this log backup and it will >> contain all log records since before the db backup. >> >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:OyMrUChmKHA.4872(a)TK2MSFTNGP05.phx.gbl... >>> Thanks for the link Tibor, I was looking in the wrong forum. >>> >>> Now I'm having some trouble getting my head around this one. Let's see >>> if I have what the situation correct? >>> >>> -- Last good backup Oct 1 >>> -- Jan the .mdf's are toast >>> -- Have a huge .ldf file, but we don't know when it started >>> -- Get the .ldf file on a system with a (basically) blank .mdf file and >>> do a NO_TRUNCATE log backup. >>> -- Restore the Oct backup >>> -- Restore the log backup you just created? >>> >>> How does the log backup you just created work as an unbroken chain from >>> the Oct backup? After all, the datestamp on the 1st log record is >>> unlikely to match up to the Oct backup. >>> >>> I don't understand how this can work. >>> >>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >>> in message news:e7D7PrgmKHA.256(a)TK2MSFTNGP05.phx.gbl... >>>> Here goes: >>>> http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/13402575-58f0-4985-9853-3e687372aba2 >>>> >>>> Hmm, it seems Richard did reply there, but I wasn't notified by email - >>>> hence I didn't see hat reply. My apologies. >>>> To repeat: when you "slide in" files from a different db (the crashed >>>> database), you should only do the ldf file. SQL server should see no >>>> mdf file. The situation should be as if you have a SQL Server, stop it, >>>> delete the mdf file and then start the SQL server. Yes, the db will be >>>> suspect, but you can do the log backup using NO_TRUNCATE. >>>> If that doesn't work then either correct steps weren't followed (I >>>> believe there's a KB on this - at least here used to be) or something >>>> strange is going on. In any event, it is probably MS Support time... >>>> >>>> -- >>>> Tibor Karaszi, SQL Server MVP >>>> http://www.karaszi.com/sqlserver/default.asp >>>> http://sqlblog.com/blogs/tibor_karaszi >>>> >>>> >>>> >>>> "Jay" <spam(a)nospam.org> wrote in message >>>> news:#VRYrUgmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>> Tibor, I looked on the MSDN forums for the post you're referring to, >>>>> but couldn't find it. Would you please be kind enough to provide the >>>>> link? >>>>> >>>>> Thanks, >>>>> Jay >>>>> >>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> >>>>> wrote in message news:Ou9caOfmKHA.5728(a)TK2MSFTNGP06.phx.gbl... >>>>>> It seems you didn't follow my advice from the MDN forums. Delete >>>>>> *both* files and slide in *only* the ldf file. Then make sure you do >>>>>> the log backup using NO_TRUNCATE. See my reply in MSDN forum for >>>>>> elaboration. >>>>>> >>>>>> -- >>>>>> Tibor Karaszi, SQL Server MVP >>>>>> http://www.karaszi.com/sqlserver/default.asp >>>>>> http://sqlblog.com/blogs/tibor_karaszi >>>>>> >>>>>> >>>>>> >>>>>> "Richard Mueller [MVP]" <rlmueller-nospam(a)ameritech.nospam.net> wrote >>>>>> in message news:#LUYH2YmKHA.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 >>>>>>>>> -- >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >>> >>> > >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 5 Prev: dfrgifc.exe & dfrgntfs.exe starting for some reason Next: Import data from Access to SQL |