Recover records from ldf file.
in [Server Configuration]
|
From: Tibor Karaszi on 20 Jan 2010 12:12 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 14:17 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 14:58 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 15:38 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 15:52 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 |