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?

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.