4

we do full backups daily. what is the point of doing full backups if we can just do differential? this is a newbie question, i do not mean for this to be too much of a profound question. your answer should be simple,.

Improve this question

3 Answers 3

Reset to default
8

You need a full backup in order to do either differential backups and/or transaction log backups. I think an example on how you can use them would be more beneficial.

Let's assume you have a 500GB database. Performing a full backup every night would use a high amount of system resources. Unless all of the data is constantly changing, the only thing you really need is the last days worth of changes. This is where a differential may come into play. What you might do is take 1 full backup on Sunday. All 500GB. Now Mon - Sat, you can perform a differential backup each night. What you'll end up with is a cumulative backup of changes to the database each night from the last full backup that are significantly smaller in size. So on wednesday, you'd have mondays, tuesdays, and wednesdays changes all rolled up in wednesday nights differential backup. To restore, you'd restore the full backup from sunday, and then the wednesday differential backup.

Now let's say you have a recovery point objective (how much data loss is acceptable) of 1 hour for your data. Just having a full and diff backup 1 time a day is not sufficient. This is where you could implement transaction log backups into the mix. You could perform these daily every hour. So let's assume you have a failure at wednesday 3:15 AM and your full backup is done Sunday at 1AM and diff backups are performed at 1AM daily. To recover you'd have to restore Sundays full backup, Wednesdays diff backup, and then the 2 transaction log backups (2AM and 3AM). This scenario would get you back to 15 minutes before the failure.

Hope this helps you understand the difference and how you can use the different backup types.

Improve this answer
3

Differential backups allow you to have an intact backup chain without using as much space as a full backup would. They still require a full backup to complete a restore, because the differential backup backs up the differences between the database and the last full backup.

Improve this answer
2
  • so i dont understand whats the difference between differential backup and transactional back up if they both depend on full back up
    – Alex Gordon
    Oct 27, 2010 at 18:11
  • 2
    Differentials depend on the full, and transaction logs depend on the full or differential (and backup the log, not the database itself). For example, let's day we had Saturday fulls, daily midnight diffs, and transaction log backups every hour. We want to restore to Tuesday at 10AM. First, we'd restore the Saturday full, then the 12AM Tuesday diff. Now, we apply the hourly transaction log backups from Tuesday until we reach 10AM, effectively "replaying" those transactions.
    – phoebus
    Oct 27, 2010 at 18:19
2

Differential backups rely on a full backup in case of a restore. It only has changes. Depending on the integrity you need you can use differential backups to provide more point in time restore capabilities.

I have seen anything from differential done nightly and then full's done weekly, to fulls done nightly and differentials done a few times a day.

In the end, like any DBA question, it depends on your needs.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .