Sql-server – Frequency of SQL DB Full and Differential backups

backupsql server

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.

Clean Full Backup every night at 1AM, older than 7 days
Full Backup every night at 2AM
Differential Backup every 1 hour between 5AM to 10PM
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?

Best Answer

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

Related Solutions

Sql-server – Understanding Backup Types

Kin is correct to point you to Ola Hallengren's backup and maintenance solution.

It sounds like you're new, so also consider looking into:

SQLServerCentral's Top 6 Myths of Transaction Logs.
Paul Randall's Transaction Log blog entries
Paul Randall's Backup and Restore blog entries
- Mandatory: Read A SQL Server DBA myth a day: (30/30) backup myths
Kimberly Tripp's Transaction Log blog entries
- Mandatory: Read Transaction Log VLFs – too many or too few?

Aaron Bertrand is correct to point you to Why Does the Transaction Log Keep Growing or Run Out of Space?.

Usr is correct; until you test restores, assume your backups are worthless.

The commenters are also correct, you must consult with the business.

In more detail, you need:

RPO: Recovery Point Objective. How much data loss is acceptable - i.e. restore back to within an hour before the loss? A day?
RTO: Recovery Time Objective. How long it takes to get the system back up again.
At least a skeleton DR plan, in particular, what types of "disasters" are your RPO and RTO good for?
- corrupt database
- hard drive crash
- multiple drive crash resulting in RAIDset loss
- accidental file deletion
- server dropped during move
- server stolen
- building burned down
- regional natural disaster (large hurricanes, earthquakes, typhoons, tsunami, major volcanic eruptions, major flooding)
Budget
- After you get this, you can go back and renegotiate the previous points.

As far as a general backup plan, I would start with considering:

Search for anywhere someone's truncating the log... and stop that! You don't want to breaking your log chain!
- Likewise if you're switching in and out of SIMPLE recovery model.
Figure out where your backups need to go; backing up to the same disk spindles that hold your databases or logs is pretty pointless by itself; lose the disks, and you lose both the active database and the backups all in one fell swoop.
In all cases, set CHECKSUM on.
Full backups for everything, regularly.
- Maybe this is daily, maybe it's weekly, maybe it's biweekly, maybe even monthly.
- NOTE: Keeping more than one around is useful; for FULL (and BULK-LOGGED if there are no bulk-logged changes) recovery model databases, you can skip a corrupt full backup if you have a prior full backup and an unbroken log chain.
Full backups are the ONLY backup allowed for Master.
- Timing on this must be at least as frequent as your RPO.
Don't forget to back up msdb too, and you might as well toss model in.
- yes, model. Sometimes it has user defined types, etc.
Differential backups on SIMPLE mode databases
- Timing on this must be at least as frequent as your RPO.
OPTIONAL: Differential backups on FULL and BULK-LOGGED databases
- these can allow you to have faster restores
- these can also allow you to "skip over" damaged/lost/corrupt transaction log backups that are BETWEEN your full backup and a DIFFERENTIAL, after which you can continue restoring transaction logs as long as your log chain is unbroken from that point.
Log backups on all FULL and BULK-LOGGED database.
- Timing on this must be at least as frequent as your RPO.
- This is MANDATORY in order to keep t-log size down
Run test restores; it doesn't matter if they're on the same server or a different server, just run them.
Who has access to the backups
Encryption of the backups
- Key management of the encryption
Offsite storage
- And how that affects RTO
  - during the "larger" disasters; even so much as a blizzard or mudslide can add hours or days to this.

Sql-server – SQL Server Differential Backup Bloat

If the full backup just grows by a few MB but your DIFF is 200MB then you're probably doing a lot of UPDATES which change the page, thus forcing the page to get copied to disk. However, full backup size won't change much as it copies all the pages anyways. This is ignoring the transaction log which in the comments you've already specified has very little fluctuation. Are you perhaps compressing your full backup but not your diff? Filestream?

At 8K a page you're looking at 25,000+ pages changed. Is this a high percentage of your database or table size? Is it very UPDATE heavy? That's most likely what's causing it based on the available info.

Can you record a extended event session or trace for UPDATES and also check to see what tables are the most updated? If for some reason you simply are not able to review the data live perhaps you can keep recording 'last updated' on the table and see which one get's a lot.

This is assuming the cause is from DB activity and we simply can't collect the changes live to troubleshoot. That's one way I'd deal with it, or open up the backup file and see what pages it has