Video Screencast Help

Backing up an sql server

Created: 27 May 2013 • Updated: 27 May 2013 | 3 comments
crp's picture


I am running BE 2012 and trying to backup an SQL server 2008.

I have several questions after playing with the agent for a few hours :

  • My full backup is 40GB big. A couple of minutes after taking a full backup, I run a log backup. Size is 10GB. Then right after that I run a differential backup. Size is 20MB. Why is the log backup so big ? It is clearly impossible that 25% of my database content changed in a few minutes.
  • What's point of taking differential AND log backup ? I clearly see the advantage of the log backups that allows to go back in time whenever we want beteween the full and the log backup. So why also take differentials ?
  • Also if it is a good practice to have log backups and differentials, when do you shedule the them ? at the same time ? right after each other ? Please share with me the schedules you have set for the 3 backups (full, log, differential).
  • On my sql server, I have DBs that have a recovery mode set to Full, while others have a simple recovery mode. So I added log backup to my original backup exec job. Now instead of having a warning saying that "XXXX is configured to maintain transaction logs. Transaction log backups are not being performed.", I get a warning saying that "A log backup was attempted on database YYYY that is not configured to support log backups. To change the configuration, use the SQL administration tools to set the recovery mode to Full.". Is there way to keep DBs in full and in simple recovery mode, and avoid having exceptions ? I also would like to avoid having 2 different backup exec jobs in which I would have to add manually the DBs.
  • Finally, could I see what retention you have set to make sure I dont do a setup where log/differential would be deleted before the full backup ?

Thanks for the help

Operating Systems:

Comments 3 CommentsJump to latest comment

pkh's picture

1) Differential backups capture the changes to the database since the last full backup.  Log backups logs the changes to the database since the last time the log was truncated.  Hence depending on when you truncate the logs, i.e. do a log backup, the log backup can be bigger than the differential backup.

2) It is a lot faster to restore a differential backup.

3) You normally schedule your log backups AFTER the full and differential backups because you do not need the logs to recovery your databases.  When you schedule your full and differential backups depends on your installation's requirement.  You can just run full backups if your backup window permits.  You don't have to run differential backups.

4) Unfortunately, you can't.  You got to maintain 2 jobs, one with log backups and one without log backups. The only other way to set all your databases to simple recovery mode.  However, before you do so, check with the database owners.  Some applications requires the databases to be in full recovery mode.  You cannot set all the databases to full recovery mode because at least Master and Model database must be in simple recovery mode.

5) If you are backing up to disk,  DLM will prevent your log and differential backups from being deleted before the next full backup.  If you are backing up to tape, then you would have to adjust your OPP accordingly.

You might want to search the Web for more details on SQL recovery modes.

crp's picture

Thanks pkh for all these answers.

One more :)

If I keep a single backup job for DBs that are in simple and in full recovery mode, we saw I'll always get an exception when trying to do the log backup on DBs that dont support it. Is it a problem ? besides the fact that this will pollute the logs...

pkh's picture

No.  There is no harm, other than the warning messages.  However, due to the warning messages, you would have to look closely at the job log to see whether there are other errors.  If you have a clean job log, then any exceptions are immediately flagged, so you would know that there is an error.