Solved

size of DB for diff backups

Posted on 2011-02-22
5
367 Views
Last Modified: 2012-05-11
at what size of the database is differential backups more recommended?

(like >100 GB etc)- because if the database is smaller, the full backup will be fast enough.
0
Comment
Question by:anushahanna
5 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 125 total points
ID: 34950050
I personally won't prefer Differential backups and would go for Full Backup with Transactional Log backup.

Say if you have Diff backup scheduled to run every 2 hrs, then

First Diff Backup = X size
Second Diff Backup = First Backup + Y Size
and so on..

Thereby occupying more of your disk space and your disk utilization is not good.
Instead having a full backup with purging older full backups or full backups with transactional log backup would be a good idea..
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 125 total points
ID: 34950231
Agree with teh above and if you have a Dr backup and are doing backups look to log ship your tran logs to cut your restoration time down.... whilst i know a lot of people do like them ive never been able to get value out of diff backups when looking at the space implications required for storing them
0
 
LVL 9

Assisted Solution

by:rajeevnandanmishra
rajeevnandanmishra earned 125 total points
ID: 34950436
Hi,

While agreeing to the above comments, I would still strongly believe that taking the differential backup is not about only the size of database. The differential backup, run over the transaction log and  take only the backup of the activities performed after the last Full Backup. And that is comparatively very very small, considering any database size.  Also the time in which you should schedule your differential backup, is actually the duration for which a maximum loss is acceptable. Like if you have scheduled the diff backup for 1 hour, then you are accepting that a maximum loss of 1 hour is acceptable to the user.

0
 
LVL 4

Assisted Solution

by:rocky_lotus_newbie
rocky_lotus_newbie earned 125 total points
ID: 34952315
It varies froom Database to Database depending on the data access patterns. FOr some databases, if most of the pages are updated between successive differential backups, then the differential back up is as big as the full backup. In this case, it is not ideal. On the other hand if the number of pages modified is small, differential backups are best bet.

The Main advantage of Differential Backups is for Data Recovery.

I normally configure differential backups for every 4 to 6 hours. Just to make sure the recoveries are faster in case of a disaster.

However, if there is space constraint, we need to go with just the Transaction Log Backups.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34953130
thanks for your helpful feedback.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question