February 10, 2010 at 2:15 am
Hi Folks,
I had one data base for which I had kept .mdf files on D drive and .ldf files on E drive.
Now what happen the other night was my D drive got crashed and I am now left with on .ldf files on E drive :crying:
I need to restore my database and I don't have my .mdf files.
Is there any way by which I can restore my database using only .ldf files ??
Thanks
Sunil
February 10, 2010 at 2:41 am
No, I'm sorry. You should have a backup.
-- Gianluca Sartori
February 10, 2010 at 2:57 am
Thank you for your reply.
But isnt there any any way i can restore the db withou ,mdf ?
February 10, 2010 at 3:00 am
Yes: a database full backup is suitable for that purpose.
Do you have any?
-- Gianluca Sartori
February 10, 2010 at 3:15 am
You have to have a Full Backup in order to restore - the Log file only records any changes made since the last database backup.
Piecing together a database from the log is not possible. It doesn't have the table structures, indexes, data and suchlike required to re-create a database.
BrainDonor.
February 10, 2010 at 4:59 am
sunil.aggi (2/10/2010)
I need to restore my database and I don't have my .mdf files.Is there any way by which I can restore my database using only .ldf files ??
Nope. If there has never been a backup, then the transaction log will only contain very recent changes, hence there's nothing in there to recovery.
But isnt there any any way i can restore the db withou ,mdf ?
It's called a database backup. Any database that is even slightly valuable and cannot be recreated completely and trivially from another source should have backups.
No backup = no database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 5:01 am
BrainDonor (2/10/2010)
the Log file only records any changes made since the last database backup.
No it does not. The log file records changes since the last log backup (in full/bulk-logged recovery) or since last checkpoint (simple recovery)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 5:05 am
GilaMonster (2/10/2010)
BrainDonor (2/10/2010)
the Log file only records any changes made since the last database backup.No it does not. The log file records changes since the last log backup (in full/bulk-logged recovery) or since last checkpoint (simple recovery)
Yeah, and whats more - I knew that! That's what I get for trying to simplify something too much - I talk rubbish.
Thanks Gail.
BrainDonor.
February 10, 2010 at 12:39 pm
you know, I do seem to recall reading something somewhere about an undocumented command that might let you recover a database from a backup made after the database got hosed... but in this case, it seems that the database is just gone. As Leonard McCoy would say: "It's dead Jim"
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 12, 2010 at 9:56 am
Years ago I found myself in a similar situation. The database was a large database for the time period, small in todays terms. The database had grown in size and the backup device originally purchased for the database server no longer had the capacity to backup database. I was proactive and informed management of the issue before the database had outgrown the device that shortly the device would no longer suit our needs. Time went on and I was ignored by management. Every week for the next year I brought up the fact that we had no backups. This database was part of a mission critical reporting system that management used to make key decisions. I knew the impact that the business would have if they'd loose this server and started loosing sleep over it.
Then it happened. The raid V array that had the .mdf's on it lost 2 drives and all the data was lost. At the moment of disaster management had forgotten about the weekly warnings I had given them. All they cared about was that the system get back up and running. There was a lot of finger pointing and all of that pointed to me. I was the DBA and it was my responsibility. I took the responsibility.
I didn't get fired because of all the email chains and all the meetings I had had where I had given warning. The other reason I wasn't fired was because I had all the DDL backed up and was able to reload most of the data from other datasources and spent a full weekend, monday and tuesday with little sleep getting the system back in place. After 4 excruciating days and nights the system was about to 90% of what it had been and the 10% data loss was acceptable.
I worked there for another year then found a new job. It has been many years since then I have vowed never to get into that same type of situation.
Not only are the backups important but testing restores are essentials. Knowing that I can bring everything back in a disaster situation allows me to sleep at night.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply