October 20, 2008 at 11:34 am
Hi,
We suffered a crash not too long ago and was unable to recover our days work because the Disk hardware where the SQL server database was completely dead. We were able to restore from the previous days backup, but the transaction log was on a drive that died.
I was wonder what would be the best solution to protect the log file in the future without degrading performance. Please be specific.
October 20, 2008 at 11:44 am
If you need to be able to recover to a point in time your database(s) should use the Full recovery model and you should be doing regular log backups, obviously to a separate drive. How often you do transaction log backups is determined by how much data you can afford to lose as a business.
Ideally you can backup to disk and then use xcopy to copy the backups to another network location. You would also backup your backup files to tape or whatever you use for your non-sql backups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2008 at 11:48 am
Typically, backup to separate physical disks on the same server. Place log and databases files on separate physical drives as well. Having mirroring or raid for hot swappable drives is ideal. Depending on transaction volume, you may not want the log files on Raid 10 but it depends on your environment and the amount of data loss that is deemed acceptable by your user base. Backup logs as often as is necessary to minimize data loss. Sweep backups to tape or copy to a remote server to ensure recoverability. TEST anything that you decide to put into place on a regular basis to prove it works.
Edit: Jack beat me by a few minutes 😛 but the summary of each of our responses is pretty much the same.
-- You can't be late until you show up.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply