1

It's said differential backup could be restored only after it's base. It sounds like I just divide the one step full restore into two steps.

And I can't see the benefit here.

How can i use with differential backup of SQL Server?

BTW, I'm looking for a solution to restore my data from the primary database to the standby one only with the differential data everyday. It's quite appreciated for any suggestion.

Improve this question

2 Answers 2

Reset to default
1

Differentials are useful only in the presence of log backups. A restore starts from the last full, and then all log backups are applied. In a typical scenario of backing up the log, say, every 15 minutes, the number of log backups to be applied can quickly escalate. Having differentials allow the sequence to be shortened to last full -> last differential -> all log backups after that.

I'm looking for a solution to restore my data from the primary database to the standby one

That is called Log Shipping and is built into the product. You must change the recovery model to full (or at least bulk_logged) and start taking log backups. There is just no way to avoid this.

Improve this answer
1
  • Thanks so much.I just ran into a mistake of rouphly thinking Log Chipping is real-time with high cost
    – Wayne Wu
    Commented Jan 19, 2013 at 13:07
0

As it's clearly stated here: Row-Overflow, Differential Backups, and More

Restoring a differential backup is essentially the same as restoring all the log backups in the period covered by the differential backup.

If you're using differential backups, a full database backup created before the first differential database backup is a must. It starts the chain. But the transaction log backups are not necessary. They only provide recovery to an exact point in time.

The differential database backups provide the recovery to the time when the differential backup was taken

The advantage of having differential backups is reducing the restore time. The downside is that they can take up more space than log backups, as they are cumulative, not incremental. They contain all the data that have changed since the last full backup, not since the last differential backup.

Here's a great example: Importance of how often you take full backups, check out the Example Strategies section

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.