October 26, 2004 at 7:36 pm
Is it possible to restore an .ldf file onto .mdf file? My concern is that if I have a backup from 10pm and a transaction log from 1pm the following day and the server dies mid morning, is there a way of restoring the .bak file and then the .ldf file (not the .trn file)?
October 26, 2004 at 10:12 pm
Not sure that I understand the timing of your events - I assume that you mean that the next transaction log backup is scheduled for 1PM but the server dies during the morning. So you end up with the backup from last night and a valid .ldf file.
You cannot simply attach the .ldf file to the backup. What you will need to do is to backup the transasction log file (i.e. create the .TRN file), restore the backup from last night and the the .trn file.
Having a look at topic "How to restore to the point of failure (Transact-SQL)" in book online.
October 27, 2004 at 1:18 am
Your assumption on timimgs was correct. But how do you back up a .ldf, if for example the .mdf file is not there (due to a disk failure for example). My .ldf's and .mdf's will be on physically different drives.
October 27, 2004 at 1:48 am
Hi Steve,
if I understand you right, you make backups of your datafile (.mdf) and transaction log backups, but once the datafile is lost you'd like to make another transaction log backup in order to recover the latest transactions.
I'm afraid that once your .mdf is gone you could still copy/backup the .ldf but it won't help you, because when you reattach the ldf you will get an error that the LSN is not valid.
If you're using several files and you loose one of the secondary files (.ndf) in some situations it's still possible to make a transaction backup. But not if your primary datafile (.mdf) is lost.
M
[font="Verdana"]Markus Bohse[/font]
October 27, 2004 at 2:26 am
To maybe clarify things, for example:
1/1/04 10pm Full DB Backup
2/1/04 1pm Transaction Log Backup
Disk with .mdf files on it dies on 2/1/04 at 10am. The .ldf's are on a different disk and are still accessible.
I can restore from 10pm the previous night no prblem. But how do I backup the current .ldf (as say a .trn file) and restore this on top of the restored .bak file?
October 27, 2004 at 7:35 am
t
Tajammal Butt
October 28, 2004 at 6:47 am
After the RESTORE DATABASE command to utilize your database backup from 10 pm, issue a RESTORE LOG command. (See RESTORE in the BOL)
In some cases you might be able to do one last BACKUP TRAN to catch the latest changes since the last tlog dump but that depends on the db down situation. I wouldn't bank on it to be there all the time.
October 28, 2004 at 9:39 am
Even if you lose the .mdf file you might be able to do a log backup. Remember the schema is kept in MASTER, so when the backup is run, Master knows where the log file is and might be able to back it up.
Try this to test it:
1. Create a new database with one table
2. Backup the database
3. Do some inserts, updates, deletes to increase the log
4. Delete the .mdf fiile
5. Try to do a log backup
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply