Solved

size of DB for diff backups

Posted on 2011-02-22
5
366 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now