October 16, 2003 at 10:23 am
Hi everyone,
Lets say I have my data on drive C, my indexes on drive D, and my logs on drive E. Now, assuming that there is no RAID or mirroring, if ANY of these drives fail, then I cannot restore to the last commited transaction, right? What happens if the data drive fails, can I retore to the last commited transaction?
October 16, 2003 at 11:41 am
Any one of them failure will cause your database corruption and stop working.
What do you mean "can I retore to the last commited transaction?"
October 16, 2003 at 11:50 am
Hi Allen,
I know that if my log and index drives fail, then I would have to restore my last backup, but I cannot apply the logs that were generated after the last backup.
What I want to know is if my data drive fails, can I restore my last backup and apply the logs that were generated since that last backup or not?
Thanks.
October 16, 2003 at 11:57 am
Indexes is part of your database and it will be in either MDF or NDF files, Doesn't matter which drives it resides.
quote:
I know that if my log and index drives fail, then I would have to restore my last backup, but I cannot apply the logs that were generated after the last backup.
Why not? As long as you restore last full backup and backups of transaction logs with non-recovery option, you should be able to restore last transaction log backup.
quote:
What I want to know is if my data drive fails, can I restore my last backup and apply the logs that were generated since that last backup or not?
Sure you can.
October 16, 2003 at 1:50 pm
Thanks Allen for the help.
"Why not? As long as you restore last full backup and backups of transaction logs with non-recovery option, you should be able to restore last transaction log backup."
So if I made a full backup on monday and a log backup on tuesday and on wednesday the index drive fails, how would I restore the database to the last commited transaction? Don't I need to backup the logs (from tuesday through wednesday) in order to restore them?
Also, in regards to the same scenario, if my data drive fails, how can I restore to the last commited transactions?
Thank you.
October 16, 2003 at 2:12 pm
http://support.microsoft.com/default.aspx?scid=kb;en-us;253817&Product=sql
Suggest you backup transaction log more frequently if possible.
October 16, 2003 at 2:32 pm
From BOL.
"How to back up the transaction log when the database is damaged (Transact-SQL)
To create a backup of the currently active transaction log
Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:
The name of the database to which the transaction log to back up belongs.
The backup device where the transaction log backup will be written.
The NO_TRUNCATE clause to back up the transaction log without truncating the inactive part of the transaction log.
This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file(s) is accessible and undamaged.
Optionally, specify:
The INIT clause to overwrite the backup media, and write the backup as the first file on the backup media. If no existing media header exists, one is automatically written.
The SKIP and INIT clauses to overwrite the backup media, even if there are either backups on the backup media that have not yet expired, or the media name does not match the name on the backup media.
The FORMAT clause, when using media for the first time, to completely initialize the backup media and rewrite any existing media header.
The INIT clause is not required if the FORMAT clause is specified.
Important Use extreme caution when using the FORMAT or INIT clauses of the BACKUP statement as this will destroy any backups previously stored on the backup media."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply