Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

size of DB for diff backups

Posted on 2011-02-22
5
Medium Priority
?
375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 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 500 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 500 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 500 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

609 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