September 13, 2011 at 5:32 am
I have a a question regarding back-up files in maintenance plans, which i am struggling to find a black and white yes or no for.
I currently have the same maintenance plan configured for all of my companies production database servers (2005, 2008 and 2008R2). All production databases are set to "Full" recovery model, with a maximum data loss requirement of 4 hours.
My plan is as follows.
Friday 16:00 - Transaction Log (set to append the weeks 4 hourly transaction log backups).
Friday 20:00 - Full backup (set to overwrite the previous Fridays full backup).
Differential (set to overwrite the previous weeks daily differential backups).
Transaction Log (set to overwrite the previous weeks 4 hourly transaction log backups).
SSMTWT 20:00 - Differential (set to append to the weeks daily diffential backups)
SSMTWT 4 hourly - Transaction Log (set to append the weeks 4 hourly transaction log backups).
Each backup task backs up to its relevant backup file on a per database structure i.e. mydb_Full.bak, mydb_Diff.bak and mydb_trn.trn. These files are in tern are backed up to tape as part of a differential and weekly full backup (scheduled in between each SQL DifF or Full backup).
My question is:
Weekly the existing files are overwritten - If I had to restore the files from tape, woudl I be able to see each individual diff and transaction backups within their relevant files? for example if i needed to restore a mid week and mid day diff and transaction log backup. Or would it be better to create individual files for each database backup task i.e. a new file for every diff and trans?
I hope this makes some sort of sense, and I welcome any feedback anybody can provide.
September 13, 2011 at 7:54 am
I prefer to have individual files for evey backup event. It creates lots of files, but is easy to see what happened & when.
September 13, 2011 at 8:26 am
A single file also means if the file gets corrupted, you lose one backup, not all of them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2011 at 9:19 am
Thankyou for your replies, definitely something to think about with regards to the file corruption!!
Do you have any thoughts on the initial question? Which essentially is: Can you restore a set of backup files from tape archive, when each file contains multiple backup events/sets, and perform a "point in time restore"? for example file 1 contains 1 full backup, file 2 holds a weeks worth of daily diffs and file 3 holds a weeks worth of 4 hourly transaction log backups.
Can SQL view the contents of each file and use this to perform a "point in time" restore?
Thanks again in advance.
September 13, 2011 at 9:30 am
Yes, but short of not taking any backups, I think that's the worst way to go about this.
September 13, 2011 at 9:44 am
As others have said, it's best to make each backup to a separate file. If you have more than one backup in any file, however, you can use RESTORE HEADERONLY to find out what backups are there.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply