November 30, 2012 at 7:12 am
Guys,
Let me begin by stating, I am rookie when it comes to SQL, I am just starting to learn SQL and I apologize if this is a rudimentary question or silly even.
I was asked by a colleague what would be my Back up Plan for a 2TB Database?
My response:
Depending on the resources available would depend on the plan of backing up a 2TB DB. But, if we had to rely on Native SQL, my plan would be as follows:
( I will make the assumption that this is a Critical DB that must be up and running M-F during business hours, also DB is in Full Recovery Model, and we have the ability to take tape backups. )
Full Backup on a Nonbusiness Day, Preferbly Sunday.
Differential Backups Mon. -Sat. every 6 hours.
T-Log Backups every 2 hours Mon - Sat
Back Up to Tape once a week, Saturday.
Is this a good plan, mediocre, or am I way off?
Thank for the responses guys, I appreciate the help in understanding.
November 30, 2012 at 7:24 am
It depends on the business requirements. If you do log backups every two hours, you're saying you can afford to lose two hours' worth of data in the event that something goes wrong.
I think six hours is a bit too frequent for differential backups. Don't forget that a differential backups up every page (or is it extent?) that has changed since the last full backup. If your database is highly transactional then you could find that diff backups approach the size of the full backup by the end of the week. Filegroup backups are something you could consider to lessen the impact of each individual backup operation.
If you do index maintenance on the database (and if you don't, you should) then make sure this is scheduled between the last differential backup and the full backup.
John
November 30, 2012 at 7:36 am
Thanks for the reply, I appreciate it.
Within the conversation I was having, File Groups came up and I said we could use do that. What I am not sure of, should we always use File Groups when backing up databases of this size?
November 30, 2012 at 7:45 am
It depends what works best for you. I must admit it's not something I've ever used myself. If you only have the default (PRIMARY) filegroup then there's no point. Bear in mind that you still need a full backup, and it may make your recovery a bit more complicated.
John
November 30, 2012 at 7:53 am
Thanks again,
I appreciate the time it took you to help me out.
I will continue on my research and understanding of the different methods in backing up databases.
November 30, 2012 at 7:57 am
Great suggestions. Can I ask what version of SQL Server you are using? Depending on your version you may have the ability to compress your backups with SQL's built in compression. If not, do you have access to any 3rd party compression tools (i,e. Idera, HyperBac, Litespeed, etc). Using tools like these will significantly reduce (and in many cases, speed up) your backup times.
As John as already noted, 6 hour differentials may be a bit much (I do 12 hour) and since you mentioned this is a critical DB system I would recommend you perform your TLOG backups every 10 or 15 minutes (you're database is only as good as your last backup ;-))
You can backup several file groups separately. This can be particularly useful say if you have older data that can be moved to a different filegroup where you can back it up once and store it permanently to a network device/tape (then it can be excluded from future backup strategies). Doing things like this definitely makes it more complicated, but allows for more flexibility
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 30, 2012 at 9:30 am
The one thing to keep in mind when setting up any backup plan is the restore process. Because, after all, the backup is not the most important part, the restore is. So, if you have to restore from backups, can you? Filegroups as a backup mechanism allow you to break apart how the backups are run, making it much faster. But, the restore process becomes much more problematic.
I usually think about backups as a business issue, not a technical one. Figure out what the business needs and then work backwards into the technical solution to meet those needs.
"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
November 30, 2012 at 11:06 am
ofg1983 (11/30/2012)
Guys,Let me begin by stating, I am rookie when it comes to SQL, I am just starting to learn SQL and I apologize if this is a rudimentary question or silly even.
I was asked by a colleague what would be my Back up Plan for a 2TB Database?
My response:
Depending on the resources available would depend on the plan of backing up a 2TB DB. But, if we had to rely on Native SQL, my plan would be as follows:
( I will make the assumption that this is a Critical DB that must be up and running M-F during business hours, also DB is in Full Recovery Model, and we have the ability to take tape backups. )
Full Backup on a Nonbusiness Day, Preferbly Sunday.
Differential Backups Mon. -Sat. every 6 hours.
T-Log Backups every 2 hours Mon - Sat
Back Up to Tape once a week, Saturday.
Is this a good plan, mediocre, or am I way off?
Thank for the responses guys, I appreciate the help in understanding.
I would go with a full backup of every database every day, and transaction log backups every 15 minutes 24x7.
Use SQL Servers builtin backup compression for all backups.
If you need more speed for your full backup, backup to multiple files in parallel.
Backup your backup files to tape every day.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply