Lost the MDF, have the LDF
-
Hi all. I had a partition take a complete dump on me and lost the .MDF of one of my databases. I have the log files on a separate drive array, so I still have the .LDF of that database. Is there a way to restore my database from the log? And to answer the next question before it gets asked: no, my NetAdmin didn't have the tape running on that server because he "didn't know" I meant to have that server backed up when I included it in the list of servers to be backed up. Also, ChkDsk /r was not able to recover that particular file (though I did get all the other data files back).
-
Unfortunately, You can't.
-
I think you're outta luck. There may be a long shot...if you had Full Recovery Model turned ON, and had never backed up the transaction log, and all data had be inserted/bulk inserted through normal transactions, you MIGHT be able to restore the transaction log to a blank database??? Just a wild thought.
-Dan
-Dan -
If you don't have any complete database backup, it is impossible to restore database from just .ldf.
Robert
-
Sorry, no can do. Must be able to restore from a FULL db backup before moving on to the log.
-
You never backed the database to disk but you backed up your logs? I don't know about other admins, but I think that's a pretty foolish risk you are taking there. However, having learned that lesson the hard way myself, I have our DBs backed up every night to disk and then tape. The wizard makes it very easy to set up. Do you not have enough room on another disk to back up the DB? Anyway, it is a shame to hear about you losing the MDF.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Does not sound as though any database or log backups had been made. Also not sure it is the best idea to backup the 'server', backup the database files.
-
Actually, I was assured by the NetAdmin that my server was being backed up (the partitions that hold my MDF's and LDF's) as part of the backup sets with the rest of the network. The lesson I learned is that I should have been managing my own backups, not relying on someone else to do it as part of the rest of the domain's backup... anyway, I lost the device that had the MDF on it, but the LDF devices did their jobs. The tape set that was supposed to have the files on it was never actually activated, so the job was there, but was never run.
-
Lessons Learned: Never have backup of MDF on same disk as live database. Been there, done that.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Hi there
The admin comment, this is a classic case of "roles and responsibilities" that have been lost and forgotten. I dont trust any sys admin to do their job, even my best mates that have been working in the industry for years :-). Always backup via sql-backups, and try and duplex the backups to another server to disk array. I use raid arrays for all servers (dev, test etc..) so some of the risk is mitigated but its far from the only answer.
One problem to consider also is in relation to where backups reside, there is nothing worse than doing everything right then the disk array packs up and you cant get it up and running again, only to remember that your disk backups also reside here and you have to resort back to tape (which can blow out your recovery time SLA).
Take care also with tape backups. If admins are using SW to do it, is this SW installed on other servers? tape formats compatible? the restore issues here can be a nightmare to deal with if its not tested often. I have seen some classic cases where SW was upgraded time and time again, but the time between restores was months, only to find that 1 small SW patch resulted in a backup that couldnt be read on any other server (unless they also went through the identical steps of patching). Not fun!
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Not sure I agree about not backing up to same drive. You run the risk of it not getting pushed to tape no matter where the backup resides. A better practice if you can manage it is to copy to a different server once the backup has run (in my view anyway) and to keep two days on disk in case a tape fails.
Andy
-
If your database was in 'Full' recovery mode, then your transaction log may well contain all of your transactions.
You might then be able to resurrect a lot of the data by using a tool such as Lumigent Log Explorer to script the transactions.
If you were using 'Simple' recovery model, then forget it.
-
Well, if you can afford (and well should afford) another server, I definitely AGREE that you should back up to it. Hey, by the way, technically speaking, when you think about it, that is another drive.
Edited by - dalec on 06/23/2003 06:59:27 AM
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
ianscarlett,
eljeffo never did a full backup, ever. To my knowledge and according to what I've read in the BOL, you can only restore a transaction log to a full backup.
-SQLBill
-
quote:
...my NetAdmin didn't have the tape running on that server because he "didn't know" I meant to have that server backed up when I included it in the list of servers to be backed up.
I hate to be brutally honest but they call that blameshifting. Learn from not backing up your MDF and leave the blame out (Disaster Recovery 101).
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply