August 26, 2003 at 12:11 pm
Hi everyone, I need your expert advice on something. If I have all my sys databases (master, msdb, model, etc.) on one drive, all user databases on another drive, and all user logs are on a third drive, and lets say the drive holding the sys databases crashes, would I be able to restore from a full backup, apply all logs that have been backed up, and also apply ALL THE LOGS THAT ARE ON THE THIRD DRIVE to roll forward all transactions? Thanks for your input.
August 26, 2003 at 7:18 pm
Not sure I understand, so bear with me if this doesnt answer it entirely. The location of the logs doesnt matter, as long as you have a full backup and the entire set of logs you need to restore, you can do it. After correcting the failed drive of course (otherwise you have to move stuff, which does complicate things).
Andy
August 26, 2003 at 9:32 pm
if you have your master backup, then yes you will be able to fully recover your system to it's prior state. However if you do not have a master backup, then you will be able to recover all your user databases but some of your server settings and your logins may be incorrect. All this is fixable, but time consuming, so always make sure you have a backup of master.
August 27, 2003 at 11:20 am
Thanks for your responses. I am not sure if the way I put the question is understandable. So, I am going to give it another shot:
Drive 1 has: master, msdb, etc.
Drive 2 has: user databases
Drive 3 has: logs
My last full backup was 5 week ago. The logs were backed up 2 days ago. So the the new entries in the logs have not been backed up. Now, if Drive 1 fails, can I restore from the full backup, and apply the changes from the log backup, and here is where I am confused: Can I then apply the logs that reside on Drive 3? If I can apply the logs that are on Drive 3, then I can roll forward all changes to the last committed transaction.
Thanks a lot!
August 27, 2003 at 11:50 am
Each database has pair of database files, ie MDF and LDF. In your case, where is the LDF, Is it in drive 3? Or the drive 3 is used to keep the transction log backups? You can't apply LDF to database. You have to restore transaction log backup.
If the drive 1 fails, you will lose the system database that you have to restore from the backup. Because master database keeps information about your user database, logins etc, Once master database is restored, user databases will be reconigzed by SQL Server and you really don't have to restore them.
August 27, 2003 at 12:20 pm
Thank you Allen!
Now I know that I do not have to mirror drive 1. If the restored system databases did not recognize the databases on other drives, then I would have to mirror Drive 1.
However, I am thinking that we need to mirror Drive 3, which has all the .LDF files. This way if one drive fails, I would not loose all of the transactions that occured since the last log backup. What do you recommend? Is this a viable solution?
Thank you.
August 27, 2003 at 12:32 pm
I would definitely recommend that you mirror drive 3. But I would also suggest that you mirror drive 1. Why have something like a drive failure take down your production system, drives are cheap.
August 27, 2003 at 12:50 pm
Don't store databases (MDF/LDF) on single disk drive as single drive don't afford any fault tolerance. Instead, always choose a RAID array made up of two or more physical drives that offers fault tolerance.
System databases are very small and can fit on a single disk drive, consider RAID 1 for system databases. Ideally, transaction log should be located on its own RAID 1 array. This is because transactions logs are written to and read sequentially, and by isolating them to their own array, sequential disk I/O won't be mixed with slower random disk I/O, and performance is boosted.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply