diff.backup - OLTP/OLAP

Posted on 2011-02-18
Last Modified: 2012-05-11
is diff backup better or adviced for OLAP (where there are batch processing and bulk inserts) because it will be easy for SQL to track what has changed since last FULL backup, instead of OLTP (like
Question by:anushahanna
  • 2

Accepted Solution

rocky_lotus_newbie earned 375 total points
ID: 34928983
It depends on the data recovery requirements.

In OLTP when there are multiple pages in the database updated, the differential backup tends to be considerably huge which is not the case in OLAP. (The Log backups also might be huge because of the transactions)

However, data recovery is needed in both environments.

Skipping differential backups is not advisable in most cases. Diff backups help in making the data recovery process faster compared to the log backups.


Author Comment

ID: 34929014
>>Skipping differential backups is not advisable in most cases. Diff backups help in making the data recovery process faster compared to the log backups.

you mean Full backup+diff backup+tran log backup is faster than Full backup+tran log - and only for OLAP (where there are batch processing- huge inserts etc instead of individual transactions)
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 125 total points
ID: 34932755

i think what you should do is read this :

and try to develope a backup scheme relevant for your needs and limitations.

Author Comment

ID: 34949742

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 35
Using this function 4 38
SQL works but want to get the XML node data separately 11 25
SQL Server In place upgrade from 2012 to 2014 12 18
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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Viewers will learn how the fundamental information of how to create a table.

685 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