November 5, 2008 at 10:54 pm
Dear All,
If possible, could someone please tell how we can recover a database with only LDF file.....:unsure:
Thanks in advance.
Thanks & Regards,
Sajid C.
November 5, 2008 at 11:00 pm
I'm sorry but it isn't possible. Don't you have a backup to restore?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 5, 2008 at 11:13 pm
Dear Adi,
Thanks for your instant reply. Unfortunately, we don't have any backup to recover database.
Regards,
Sajid C.
November 6, 2008 at 1:36 am
The .LDF file contains transactions, not the data. The data is in the .MDF and .NDF file.
Wilfred
The best things in life are the simple things
November 6, 2008 at 2:40 am
I will present a scenario
:)Your Customers says to start a new production Server at 9:00 AM.
:)You started your production servers at 9:00 AM.
:)Transaction Started.
:)Database is in Full Recovery Model.
:)Your scheduled full backup is scheduled at 11:00 AM
:)Your MDF is at D: Drive and LDF at C: Drive.
:w00t:Unfortunately Server Crashed and D: is permanently damaged, the MDF file cannot be recovered.
You just have LDF file then how I can go for recovery?
Is there any way we can read Detached LDF file?
I wish all the experts and gurus should give some light on this.
November 6, 2008 at 3:06 am
Not possible.
In the .LDF file you see transactions like: (simplified version)
1) update customer set address='4th Avenue' where custid = 235235
2) insert into contract values(1,"johnson",$1000000)
3) delete from salesrep where salesrepid=26
4) create procedure usp_backup......
As you can see only modifications are in the .LDF file, no tables/triggers/procedures etc. If your lucky, only newly created objects (step 4) can be reproduced. I think Idera has a logreader tool. But this is peanuts compared to the whole database.
Wilfred
The best things in life are the simple things
November 6, 2008 at 3:42 am
rajdba (11/6/2008)
I will present a scenario:)Your Customers says to start a new production Server at 9:00 AM.
:)You started your production servers at 9:00 AM.
:)Transaction Started.
:)Database is in Full Recovery Model.
:)Your scheduled full backup is scheduled at 11:00 AM
:)Your MDF is at D: Drive and LDF at C: Drive.
:w00t:Unfortunately Server Crashed and D: is permanently damaged, the MDF file cannot be recovered.
You just have LDF file then how I can go for recovery?
Is there any way we can read Detached LDF file?
I wish all the experts and gurus should give some light on this.
I guess it's a bit late now but you should have taken a full back of the database before you started the production server at 09:00 AM.
As other's have pointed out, you cannot recover with just the ldf file. Your only option, as someone else has already mentioned, is to use a logreader tool, which will enable you to read the transactions that occurred before the catastrophic failure. But that's hardly going to give you much.
November 6, 2008 at 3:58 am
Sorry , but this is not possible, is there any backup for this database at all, like a day old or something???
November 7, 2008 at 2:04 am
Always always always remember to make your backups on a seperate array to your data files (.mdf/.ndf)
if you lose your log file (.ldf) you can recover, but to get back from a lost data file requires a restore from backup.
MVDBA
November 7, 2008 at 2:24 am
Hi,
actually, if the new production server database was empty at startup and you built the schema from zero, you should be able to recover - if no log backups with truncate were done prior to the crash.
There are utilities out there that can translate the log file back to sql statements - LiteSpeed Backup 5.0 can do it, Apex SQL Log Reader (?) can do it.
It might even be possible to attach the old log file to an new empty database and have sql server roll foreward - i am not sure about that though.
regards
karl
Best regards
karl
November 7, 2008 at 2:29 am
If this is possible, I assume your database needs to be in FULL RECOVERY (hey, it's a production database 😛 )
Interesting thought, has anybody experience with this?
Wilfred
The best things in life are the simple things
November 7, 2008 at 2:40 am
oops - forgot to mention that...
- even bulk logged mode should be ok i think.
using a log reader should work even if the db was not empty but created from a defined state that can be recreated.
Best regards
karl
November 7, 2008 at 2:48 am
not possible to restore database with .ldf file, send a query to directly microsoft sql server team.
November 7, 2008 at 9:09 am
Here's a useful method. This assumes that your backup is copied remotely on a regular basis, which I STRONGLY recommend (we use Netbackup but you can use many different programs out there, a few of them free):
1. Do full backups and have SQL Server delete after 1 or 2 days (depending on your drive space limitations)
2. Have the transaction log backups be stored for at least 72 hours.
3. Let the remote backup occur nightly, copying everything.
If something happens to your one full backup and all you have is the trans. backups, restore a previous full backup, and all the transaction log backups since then.
For Example
Full backup 1 => Sun, 3am
Trans Log backups => from Sun, 3am to Mon, 3am
Full backup 2 => Mon, 3am
Tran Log backups => from Mon, 3am to Tue, 3am
Full backup 2 gets corrupted. So, retrieve Full Backup 1 from the remote site, all trans log backups from sun-mon
(remotely if not on a local drive), and you'll have an unbroken chain from FullBackup1 to the most recent transaction
log backup.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
November 7, 2008 at 9:25 am
the following stored procedure
sp_attach_single_file_db
attachs a single file database, however it relies on the MDF not the LDF. M$ went to all the trouble of cov ering this, I'm sure if it were possible to rebuild a database from an LDF it would be noted 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply