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 |
|
fritzy
Starting Member
5 Posts |
 Posted - 2007-08-09 : 12:24:08
|
| As much research as I've done over the past week, I'm kind of amazed I haven't found a clear answer to my question (which is in the very last two paragraphs) which deals with when to use append vs. overwrite. I've been working on implementing a new backup strategy:Full backup - once a day, midnight Diff backup - twice a day, 8 am and 3 pm Tran log backup - every 15 minutes where:I'm not having to create 50 differnt tran log files and manage the deletion of files older than X days. Most of the "good" strategies people recommend avoid the maintenance plans (which handle file deletion) for using the true T-SQL commands for backup. Most of these don't include any file manangement logic and they do create 50 individual files for each 15-min tran log backup. My understanding is that I can get a away with using a maintenance plan (for the file management) and set it up this way:Maint 1:Midnight - Run Full Backup, then Run Tran Log Backup with INIT (delet files older than X days)Maint 2:8am and 3pm - Run Diff Backup with INIT (delete files older than X days)Maint 3:Every 15 minutes (starts at midnight and ends 11:45) - Run tran log backup with NOINIT (delete files yada yada yada)So, if the database crashes at 3:23pm, worst case is that I lose 8 minutes of data. I would backup the active, restore the full, restore the 3pm diff, restore the tran log, then restore the active log.My question revolves around the INIT and NOINIT when it comes to using the same files for differential and transction log backups. I'm doing the INIT (overwrite) on the tran log right after the full backup then doing the NOINIT (appending) to the file for the rest of the day. The differntial is always INIT (overwrite) since it's working from the last full backup. Do I have this right? That I can continuely append to the tran log all day but if I have a crash, that I still restore from this single tran log file (assuming I've done the full and diff restores already).Thanks,Fritzy |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-09 : 12:36:33
|
| Just create a new file with a time-stamped name with every transaction log backup. It much simpler, easier to manage, and will perform better. This is the best way to handle full and and differential backups also.CODO ERGO SUM |
 |
|
|
fritzy
Starting Member
5 Posts |
Posted - 2007-08-10 : 11:09:21
|
| Thanks for the response, but that's what all the responses say w/o explaining why. I'm curious to get to the - why does everyone say that? If I "can" get the same results using Maint Plans as I can with sprocs, what really is the benefit?How are several time-stampped files easier to manage than 1 tran log per day? This is why I have a question about INIT vs. NOINIT - if every file is it's own, there's no question about which to use, but if there was only one file...How is it easier to manage purging of old files other than a DB Maint task - I personally like to keep related functions in the same app (sprocs can delete the files, but is that truly any better)?Thanks,f |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-11 : 01:39:52
|
| It's personal preference in my point of view, I use both ways based on condition. |
|
|
|
|
|
|
|
|
Full/Diff/Tran Log Backup Plan w/ Maint Plans