Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slihp
Yak Posting Veteran
61 Posts |
 Posted - 2011-09-26 : 05:57:56
|
| We have a backup plan that takes a full backup first thing in the morning and then a transaction log back is taken every 2hr through out the day.dose this mean that the worst case senario is after restoration our database will have 2hr of data minimum. or is there a way to get data missing 2hr data back |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-26 : 06:18:42
|
| Worst case scenario is always decided by the frequency of your transaction log backups. If you take one every 2 hours it means that you can accept to loose at most 2 hours of data. If this is unacceptable to you then you need to increase the frequency of the log backups. Or introduce HA-solutions like clustering or mirroring. A tail-log backup like Senthil suggests is of course the best thing to do regardless of translog backup frequency but it will not always be possible (for obvious reasons).- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-26 : 08:06:15
|
We take TLog backups at 15 minute intervals.If you backup every 15 minutes you have 8 times as many backup files as backups made every 2 hours ... but they are about the same total disk space (allowing for some overhead on each individual file).So the only downside is that when you have to restore there are a lot more files to "specify" in the restore command.Try to have backup files on a different physical drive (and preferably controller too) to the data / log files - then if the data / log drive breaks you won't also lose your backup files  and having the Data and Log files on separate drives may also help you get a tail backup if something breaks. |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-29 : 23:01:56
|
| One important aspect of tail log backups is that your log files must be accessible. If the log file too is damaged/corrupt/inaccessible, then tail log backup will not be possible. In such scenario, all modifications after last log backup will be lost. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-30 : 05:53:49
|
| Hence my suggestion to put Data and Logs on different physical spindles ... and Backups on yet another. |
|
|
|
|
|
|
Transaction Log restore