Frequency of SQL DB Full and Differential backups

Question

I want to know what would be the best frequency in taking SQL DB - full and differential backups looking at below particular scenario.

I have seven databases and each needs to be backup.

Currently, I have setup a couple of maintenance plan in SQL as below.

1. Clean Full Backup every night at 1AM, older than 7 days
2. Full Backup every night at 2AM
3. Differential Backup every 1 hour between 5AM to 10PM
4. Clean Differential Backup Daily 10:30PM, older than 1 day

Considering the full backup size of each Database is 50GB. What would be the optimal way to take backups with 1TB of drive space.

Note that I have different drives for Full and Differential backups of size 1TB and 500GB respectively.

Any suggestions?

• Shaulinator: The setup and configuration should be driven by your requirements, the configuration should not drive the requirements. What date is the latest you are needed to restore from? How much data can be lost / will you ever need to pick a specific point in time to restore? How quickly do you need to be able to restore?
• jyao: What is your database recovery model?
• Krunal: I am novice to backups. However, it should be the default one Simple Recovery Model. Should I change that?

Answer 1

The backup policies are actually based on your RPO. But here is my suggestion. Use Ola Hallengren's backup script.

If your total size of the DB is < 1 TB

• Full Backup - daily 1AM - Retention 7 Days
• Differential Backup - Every 12 Hours - Retention 3 days.
• Log Backup - Every 15mins - Retention period 1 day.

If your total DB size> 1TB

• Full Backup - Weekly once
• Differential Backup - Daily once/twice (Based on the database load)
• Log Backup - every 15min/30mins

Answer 2

If you have a third party backup product in your organization, check it out. We do nightly full backups for a few TB of databases with several months of retention time. Commvault is able to compress the backups a lot better than SQL.

For SQL native backups I would do something like a weekly full backup, nightly differential backups and hourly log backups. But it all depends on how long your retention time needs to be.