August 2, 2013 at 12:54 am
Now we are using full backup and log backup as a backup option for our sql server.We do full backup daily once and log backup every one hour. But now i am planning to use differential backup also.But i don't know much about its performance and speed and size of backup file. So i am planning to have full backup weekly once for less changing database and have differential backup everyday. I am not leaving transaction log backup as it will help in point in time recovery.
Now i want to know is it differential backup will help me in anyway? What will be the size compared to log backup. Is there any disadvantage in using differential backup?
August 2, 2013 at 1:26 am
The most important thing is that you and your team understands the recovery procedures, so that in case of a disaster you can meet your RPO and RTO. That is, how much data can you afford to lose, and how much downtime can you accept? If you start using differential backups, you must also make sure that your team knows how to use them. Your restore operatoion will become more complex with differential backups.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2013 at 2:50 am
Each differential will be all the changes since the last full backup, i.e. they're not incremental. They can get quite big if not managed properly - even bigger than full backups.
August 2, 2013 at 3:05 am
Erland Sommarskog (8/2/2013)
The most important thing is that you and your team understands the recovery procedures, so that in case of a disaster you can meet your RPO and RTO. That is, how much data can you afford to lose, and how much downtime can you accept? If you start using differential backups, you must also make sure that your team knows how to use them. Your restore operatoion will become more complex with differential backups.
You said "Your restore operatoion will become more complex with differential backups."
How and why?
I need to restore full backup and then differential backup of the day i want and then log backups of that day. I think i won't require all log backup since from full backup.Only full backup,differential backup and then log backup till the time i require. Correct??
August 2, 2013 at 3:07 am
adb2303 (8/2/2013)
Each differential will be all the changes since the last full backup, i.e. they're not incremental. They can get quite big if not managed properly - even bigger than full backups.
How does it possible to have size more than full backup? Do you mean differential takes extra space while taking backup or something? Please clear this point.
August 2, 2013 at 3:17 am
IT researcher (8/2/2013)
You said "Your restore operatoion will become more complex with differential backups."How and why?
Because you need to restore full backup + diffbackup + log backups. So that is one step more if you only take full backups.
I think i won't require all log backup since from full backup.Only full backup,differential backup and then log backup till the time i require. Correct??
Correct. But keep in mind that you should not throw those log backups away. Maybe the last diff backup was bad, and then you can opt to restore an earlier diff backup, or only the full backup and apply logs from there.
Generally, you should keep your logs as long as you have not actually tested the backup.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2013 at 3:25 am
IT researcher (8/2/2013)
adb2303 (8/2/2013)
Each differential will be all the changes since the last full backup, i.e. they're not incremental. They can get quite big if not managed properly - even bigger than full backups.How does it possible to have size more than full backup? Do you mean differential takes extra space while taking backup or something? Please clear this point.
http://msdn.microsoft.com/en-us/library/ms175526.aspx
Differential backups are of the data that's changed since the last full backup. If you took full backups on Sunday, then differentials on the other days, with all the database churn in between full backups, by the time you get to Friday/Saturday time, you could have a very big differential backup.
August 2, 2013 at 3:40 am
Yes, I have not worked much with diff backups, but I think they are mainly useful with databases with very few changes. Beware that reindexing jobs are changes, and reindex of a big table can inflate your diff backup to the point where it is not really any gain with it.
On the other hand, if you have a database with some archive tables that you never touch, diff backups maybe useful.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 2, 2013 at 5:18 am
I want to know how exactly log backup is different from differential backup? Because the differential backup contains differences from last full backup where as log backup is also used after restoring full backup so it also contains data since from last last backup.
So the question is how these two are different? How the data is stored in both?
August 2, 2013 at 5:29 am
They are very different. A differential backup contains the pages changed since the last full backup. A log backup does not include any pages at all. Instead it contains instructions on how to roll forward transactions since the full (+ any differential) backup was restored. Because of this, you can restore the database to a certain point in time. (Unless you have ran a minimally logged operation within the time fram covered by the log.)
Another possible benefit is this scenario: there are problems in your I/O subsystem and corrupted pages are written to the MDF file. These corrupted files makes it to the backup as well. But if the transaction log is on a different disk as well as your log backups, you can recover the database by restoring the database from the point where the I/O subsystem started to thrash pages and apply transaction logs from that point. (This presumes that you restore the database on good hardware, of course!)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply