July 8, 2015 at 6:12 pm
I was asked by a friend about Backup Strategy.
He told me that they do a Full Backup once a week on a Database that is about 250 GB.
They perform a Differential every other night.
They are preforming a Transaction Log backup every 15 minutes.
In my humble opinion. IMHO, That is too many restores to recover a Database
We have enough disk space to do a full backup every night.
Perform two Differential per day to cut down on the number of Transaction Logs that are needed to restore.
I never worked anywhere where the transaction logs are created every 5 minutes.
What do you consider best practices?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 8, 2015 at 6:50 pm
The transaction log backups should never longer than the max amount of data that you willing allow to lose. Once every 15 minutes is just fine and it's not difficult to restore a large number of files if needed.
I will admit that I don't use differentials at all because they would be bigger than the whole database on any given day. Nightly fulls, tlog backup every 15 minutes works just fine for many of the databases we have. We do have one database where 99% of the a 350GB database is contained in one table. We only backup the current month on the fulls because we have all of the previous months as read-only files and it's one file per filegroup per month.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2015 at 7:42 pm
In addition to the t-log backups running as frequently as the amount of data you are willing to lose, it also depends on managing the size of your transaction log. You may be willing to lose 15 minutes of data, but you need to run the t-log backups every 5 minutes to manage the t-log.
July 9, 2015 at 1:14 am
Welsh Corgi (7/8/2015)
What do you consider best practices?
Designing a backup strategy that meets the company's RPO, RTO and maintenance windows.
It's all well and good for you to say 'I feel that log backups every 5 minutes are too frequent', but if the company's data RPO is under 10 minutes, then your recommendation of backups on the 30 minutes is flawed and will result in the company losing more data than they want. That can lead to the DBA being fired.
Likewise, saying 'take full backups every night', is invalid if there isn't a maintenance window every night that's long enough to take a full backup. Similarly with taking diff backups during the day, if they only have a maintenance window to take backups once a day, you can't suggest that.
Best practice is looking at the requirements, limitations, restrictions and then designing a backup strategy to meet them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2015 at 1:48 am
I follow the words of Paul Randal...
"The key point when planning a backup strategy is not to think about what backups you want to take – think about what restores you have to be able to perform, then work backwards from that."
Source =
http://www.sqlskills.com/blogs/paul/planning-a-backup-strategy-where-to-start/
July 9, 2015 at 8:29 am
BL0B_EATER (7/9/2015)
I follow the words of Paul Randal..."The key point when planning a backup strategy is not to think about what backups you want to take – think about what restores you have to be able to perform, then work backwards from that."
Source =
http://www.sqlskills.com/blogs/paul/planning-a-backup-strategy-where-to-start/
Exactly. As Gail said, you need to know the RPO and RTO, figure out what it would take to do restores to cover it all, and then design the backup strategy to support all that. I'll also suggest that, provided there are no other limiting factors such as maintenance windows/slowdowns/etc, that exceeding expectations the areas of backups and restores are usually not a bad thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2015 at 8:50 am
GilaMonster (7/9/2015)
Welsh Corgi (7/8/2015)
What do you consider best practices?Designing a backup strategy that meets the company's RPO, RTO and maintenance windows.
It's all well and good for you to say 'I feel that log backups every 5 minutes are too frequent', but if the company's data RPO is under 10 minutes, then your recommendation of backups on the 30 minutes is flawed and will result in the company losing more data than they want. That can lead to the DBA being fired.
Ditto on this.
If your friend is having issues determining a correct backup strategy for his business, then he needs to talk to his business about SLAs in addition to RPO, RTO and maintenance windows. A good backup plan should be part of the Business Continuity and Disaster Recovery plans. And, as mentioned in other posts, should be based not on what the backup expectations are, but on restore times and how much data the business can afford to lose.
I have actually worked in a place where transaction logs every 5 minutes was necessary. Because that's how much money the data and the peace of mind were worth to them.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply