Recover records from ldf file.
in [Server Configuration]
|
From: Tibor Karaszi on 20 Jan 2010 17:25 At least 7.0. It is a bit too late at night for me to try to think through exactly how this adds up in the old architecture (6.5 and older), but methinks that there wouldn't be much of a difference (although we had database devices, database fragmented, syslogs and all that jazz in old architecture - instead of mdf and ldf files). -- 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:Ot0EWmhmKHA.4936(a)TK2MSFTNGP04.phx.gbl... > 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 21 Jan 2010 00:33 FWIW, here's a KB on the topic: http://support.microsoft.com/kb/253817 -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:u4S2h9hmKHA.4936(a)TK2MSFTNGP04.phx.gbl... > At least 7.0. It is a bit too late at night for me to try to think through > exactly how this adds up in the old architecture (6.5 and older), but > methinks that there wouldn't be much of a difference (although we had > database devices, database fragmented, syslogs and all that jazz in old > architecture - instead of mdf and ldf files). > > -- > 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:Ot0EWmhmKHA.4936(a)TK2MSFTNGP04.phx.gbl... >> 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: Jay on 21 Jan 2010 08:59 Thanks. Bummer on the 7.0 though. "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:escLmslmKHA.2592(a)TK2MSFTNGP04.phx.gbl... > FWIW, here's a KB on the topic: http://support.microsoft.com/kb/253817 > > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:u4S2h9hmKHA.4936(a)TK2MSFTNGP04.phx.gbl... >> At least 7.0. It is a bit too late at night for me to try to think >> through exactly how this adds up in the old architecture (6.5 and older), >> but methinks that there wouldn't be much of a difference (although we had >> database devices, database fragmented, syslogs and all that jazz in old >> architecture - instead of mdf and ldf files). >> >> -- >> 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:Ot0EWmhmKHA.4936(a)TK2MSFTNGP04.phx.gbl... >>> 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 21 Jan 2010 09:48 Still have 7.0 server?... -- 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:O7b#AIqmKHA.5040(a)TK2MSFTNGP06.phx.gbl... > Thanks. Bummer on the 7.0 though. > > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:escLmslmKHA.2592(a)TK2MSFTNGP04.phx.gbl... >> FWIW, here's a KB on the topic: http://support.microsoft.com/kb/253817 >> >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:u4S2h9hmKHA.4936(a)TK2MSFTNGP04.phx.gbl... >>> At least 7.0. It is a bit too late at night for me to try to think >>> through exactly how this adds up in the old architecture (6.5 and >>> older), but methinks that there wouldn't be much of a difference >>> (although we had database devices, database fragmented, syslogs and all >>> that jazz in old architecture - instead of mdf and ldf files). >>> >>> -- >>> 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:Ot0EWmhmKHA.4936(a)TK2MSFTNGP04.phx.gbl... >>>> 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: TheSQLGuru on 21 Jan 2010 13:13 I believe ApexSQL can help you out. I know they have done some incredible recovery work for entities before. Call them and ask for Brian Lockwood and he will point you to the right person. Tell him TheSQLGuru sent you for priority handling. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "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 |