Recover records from ldf file.
in [Server Configuration]
|
From: Jay on 21 Jan 2010 13:58 I was referring to the statement in the kb article that said for 7.0 both the ldf and the mdf had to be intact. Thus, this recovery procedure is only good from 2000 forward. "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23RM60iqmKHA.5728(a)TK2MSFTNGP06.phx.gbl... > 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 >>>>>>>>>>>>>>> -- >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>> >>>>>>> >>>>> >>>>> >> >>
First
|
Prev
|
Pages: 1 2 3 4 5 Prev: dfrgifc.exe & dfrgntfs.exe starting for some reason Next: Import data from Access to SQL |