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.
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.
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.
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.
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?
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.
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?
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?
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?
Thanks
I would recommend doing a full backup after everyone stopped working, e. g. at 10 p.m. (if that is the case), not in the morning shortly before people start working. Just in order to give it enough time to run.
Personally, I prefer doing daily full backups instead of incremental backups if the database is not too big to save backups for, say, 14 days. I feel better to rely on less files. If database and full backups are too big, incremental backups might be the better choice.
As you said: How many transaction log backups you create during the day depends on the acceptable data loss window. In an environment > 5 people working on the system (just as a gut feeling) I would configure them to run all 15 minutes, on very big systems maybe even more.
After the first transaction log backup, you might want to shrink the LOG file ONCE.
I think it's not necessary to run any optimizations after a log shrink.
As far as I know it's not possible to restore transactions between 06:15 and 06:17.
When activating the transaction log backups, keep in mind that the first transaction log backup will be quite big (around the size of the current, large log). Ensure to have enough space on disk until you shrink the log file and delete the first transaction log (usually done automatically within the maintenance plan, e. g. after 14 days.).