title: SQL Server backup strategy questions for DB in Full recovery mode
body: <p>I've recently taken on the database administration of a few SQL servers varying from SQL Server 2005 to 2014, where many of the DB's are in Full recovery mode, however no good ongoing backup maintenance plans were ever setup. </p>
  
  <p>It seems to me that the previous DBA would only deal with transaction log files when they got out of control and fill up the hard drive. So i'd like to change this and fix the issue once and for all. I've been doing some reading and think I have a decent understanding of what need to be done, so i'd like to validate my understanding as well as ask a few question to clarify a few points that I still don't fully get.</p>
  
  <p>So based on my understanding to date i would need to create a maintenance plan which starts with a Full Backup. I still need to talk to management to figure out things like RTO, acceptable data loss, etc so let's assume for this example that we'll do Full Backup's on Sunday.</p>
  
  <p>Next I would add to this maintenance plan Differential backups every night... so Monday to Saturday. I realize that this could also be Full backup's or run the differentials more frequently, but again this is just as an example to make sure i'm understanding things correctly.</p>
  
  <p>Now as for the transaction log backups. I get that i would need to back these up and truncate the log file to prevent it from continually growing and getting out of control. I don't know if there are any specific recommendation for how often to back this up, but i've seen 15 minutes suggested. I guess this would more so fall under the acceptable data loss window. Is that correct?</p>
  
  <p>So the other thing that i've discovered is that when you backup the transaction log file with truncation, that if the log file has already grown out of control that it doesn't shrink the file. I've also read that it isn't good to shrink these files, at least on regular basis because once you shrink it, it would need to grow again and this will cause fragmentation and performance issues.</p>
  
  <p>Now since I am currently in a situation that the files have already grown out of control, i'd assume that i should in fact shrink the log files this one time once i've but my maintenance in place. Is that assumption correct?</p>
  
  <p>Also once i Shrink the transaction log file this one time, are there any maintenance task that i should be running to avoid performance issue due to shrinking the log files?</p>
  
  <p>One other question that i was wondering about would be with respect to point in time recovery. So let's say I take a full backup at 5:00 AM and i also take a transaction log backup every 15 minutes. I get alerted that at 6:18 AM something has gone wrong (let's say a table was deleted). So i know i can restore by Full backup that happened at 5:00 AM and leave it in NO RECOVERY mode and restore all of the Transaction log backup from 5:15 AM to 6:15 AM, but here is what i'm interested in...since i have my DB in full recovery mode, is it possible to somehow use my existing transaction log file (not the backups) to roll forward all transaction between 6:15 and 6:17 just before the table was deleted? If so how would you do this? I guess this obviously wouldn't work in the case of you loosing the hard drive with your transaction log files, or your server exploding...but in a case like i've outlined is it do able?</p>
  
  <p>Thanks</p>