SQL Server: How many different types of backup exist

Question

I never take any backup of sql server db. so please tell me how many different type of backup exist for sql server.

1. what is incremental backup? when people go for incremental back?
2. how to restore data from incremental backup ?

3) suppose from Monday to Friday some one taken only incremental backup of a large db. Saturday some data loss occur then how a person restore data from incremental backup ?
4) DBA would restore data from all incremental backup which he has taken over Monday to Friday?
5) What would be best approach for taking backup for large db?

6) what is diff file in incremental backup?
7) please discuss step-by-steps details to restore data from incremental backup

Thanks

Answer 1

Hi @T.Zacks ,

Welcome to Microsoft Q&A!
I'm not sure if you're confused about the difference between differential and incremental backups.
Incremental backup is a backup after one full backup or the last incremental backup. Subsequent backup only backs up files that have been added or modified compared to the previous one.
Differential backup is a backup that copies all the changed data since the last full backup.
So in your hypothetical example, you would need to restore the last full backup and all incremental backups from Monday to Friday. But SQL Server does not have such backups for databases except for transaction log files. You can consider differential backup.
As you can see from this screenshot, there is no type of incremental backup in SQL Server.

Using a weekly data backup schedule as an example, we could do a full backup on Monday and a differential backup on Tuesday through Friday. If the data is corrupted on Saturday, only the full backup on Monday and the differential backup on Friday would need to be restored.

What' more, please read this official document and this article which contains detailed information about each backup type.
Of course, incremental backups are just a concept, and the transaction log backup in SQL Server complies with this concept. If you want to restore the database to a specific point in time, you need restore a full, recent differential, and all the corresponding transaction log records.

Finally, there is no best approach for taking backup for large db. You can adjust to meet your needs.
Here are some tips: https://www.brentozar.com/archive/2014/07/back-terabytes-databases/

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

SQL Server has three types of backups:

1. Full.
2. Differential.
3. Log.

There is no incremental backup in SQL Server. A log backup has some logical resemblance to incremental backups, but there is also a fundamental difference.

A full back is what it says: a full backup of the database.

A differential backup includes all pages that have changed since the most recent full backup what was taken without the COPY_ONLY option.

To restore the database, you first restore the full backup and then differential backup.

Full and differential backups can be taken no matter the recovery model of the database.

Log backups can only be taken if the database is in FULL or BULK_LOGGED recovery, but not if it is in simple recovery.

A log backup backs up the transaction log (and then truncates it).

When you restore a database you must always start with a full backup, and then as I noted above, you can restore a differential backup. You can then apply any number of log backups. (Log backups are often taken frequently every 10-15 minutes or so.) What happens is that the instructions in the transaction log are being replayed. That is, you are not directly restoring pages.

This is an important distinction in the case you have corruption. Say that you take a full backup at midnight every day. On Friday you find out that the database is corrupt. You realise that this must be due to that power failure on Wendnesday. So you restore the full backup from Tuesday night, and then you apply the transaction logs, et voilà! You have a database without corruption. (Because the corruption is not in the log file, it was only on the data pages.

I should add that when you restore a backup of any sort, you need to give WITH NORECOVERY if you plan to restore more backups.

Also, I should note that while everyone takes (or should take!) full + log backups, far from everyone bothers with differential backups.