SQL Server: How many different types of backup exist

Question

question

TZacks-2728 asked Apr 21, '22 ErlandSommarskog commented Apr 23, '22

SQL Server: How many different types of backup exist

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

sql-server-general

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft · Apr 22 at 01:31 AM

Hi @TZacks-2728,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best regards,
Seeya

0 ·

Answer 1 · 2022-04-21T08:36:07.11Z

SeeyaXi-msft answered Apr 21, '22 ErlandSommarskog commented Apr 23, '22

Hi @TZacks-2728,

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.

image.png (18.2 KiB)

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TZacks-2728 · Apr 22 at 06:16 PM

few month ago i was told to restore a back then i was given a db backup and diff file. how diff file is created ? does it create every day if we take differential backup ?
you said - Saturday, only the full backup on Monday and the differential backup on Friday would need to be restored.
only Monday full back and Friday differential back would be enough ? because then how could i get data which inserted into db Wednesday, Thursday etc

Please reply. Thanks

0 ·

ErlandSommarskog TZacks-2728 · Apr 22 at 08:57 PM

diff file. how diff file is created ?

Someone takes a differential backup. Or someone has scheduled a differential backup to be taken according to some schedule.

That is, SQL Server is not taking backups automatically for you, but this is something that has to be arranged locally.

only Monday full back and Friday differential back would be enough ?

What is enough depends on what is usually referred as your RPO, Recovery Point Objective. That is, how much data can you accept to lose in case of a disaster. If you can accept to lose all input from Tue, Wed and Thu if the database goes belly-up before the diff backup, then that schedule is enough. Else it is not.

Most production databases are on full recovery, and I would say the normal is to take a full backup every night, and log backups every 10-15 minutes. This means that, assuming that your log backups go to a safe place and are not hit by the same disaster, that you lose at most 10-15 minutes of changes.

But at some places there is not really an issue with losing a day of data (presumably because it can be imported from elsewhere. In that case, they may go by simple recovery and take a daily full backup.

Note: where I say daily full backup, this can sometimes be implemented as weekly full + daily diff backup or some variation thereof.

1 ·

TZacks-2728 ErlandSommarskog · Apr 23 at 07:28 AM

Sir, my understanding with db backup is not good. suppose if i would take every Monday full backup at night 10 PM and Tuesday to Sunday take diff backup at night 10 PM. if Saturday evening suddenly data loss occur by mistake or any accident then i have Friday diff backup and last Monday full backup. so can i get full data upto Friday after restore full db backup and friday diff backup?

waiting for your next reply. Thanks

0 ·

Show more comments

Answer 2 · 2022-04-21T22:06:32.547Z

ErlandSommarskog answered Apr 21, '22 TZacks-2728 commented Apr 22, '22

SQL Server has three types of backups:

Full.
Differential.
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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TZacks-2728 · Apr 22 at 06:18 PM

Thank you so much for detail explanation.

0 ·

question