4

I have about 10 databases on a server that I have in Full Recovery model and all are mirrored using Sql Server Mirroring.

My current backup strategy is as follows:

Full Backup 1am

Differential Backup every 2 hours

Transactional Log Backup every 15 minutes

The problem I have is that the differential backups are very slow for a couple of the databases, the diff backup takes roughly 11 minutes on average.

The reason for this is the databases are synced regularly with another system for a large number of rows, thus creating a large number of differences to backup.

On the flip side my transaction log backups are obviously very quick.

This has started to become a problem as at differential backup time the server is struggling with the load, in some cases causing time outs on client machines.

What are the downsides to dropping the differential backup and going with full/ transaction backups? Obviously the restore time would increase with no diff backups but then I do have mirrored databases, so should that be a concern at all?

Any thoughts would be appreciated, the server is currently running Sql Server 2005.

Cheers

3
  • Roughly how big is the database and how big are the biggest diffs?
    – SqlACID
    Aug 14, 2009 at 14:27
  • The slow backup databases are about 3.5gb and 2.5gb. The diff backups today are 2.1gb and 1.gb respectively.
    – MrEdmundo
    Aug 14, 2009 at 15:17
  • how about log backups? Aug 15, 2009 at 16:51

3 Answers 3

3

As long as you can live with the extra time and steps in restoring from Backup and Transaction Logs in the event of total failure, the differentials are redundant. Make sure you are keeping backups and logs back far enough in case there's a problem that nobody notices right away; I like to keep 4 days of backups and logs, at a remote site, and periodically do a point-in-time restore to verify I can recover, and to see how long it takes. Prepare ahead of time, and plan for the worst, make sure you're not the only one who knows how to do the recovery.

5
  • Could I ask why you keep logs if you have full backups?
    – MrEdmundo
    Aug 14, 2009 at 16:12
  • In case I need to do point in time restores; if I backup up at midnight, and the server fails at 6 am, I can restore the full backup and then apply the logs right up to the point before the failure. Without the logs, I would lose 6 hours of work. With logs backed up and copied offsite every 15 minutes, the most I would lose would be 15 minutes of work.
    – SqlACID
    Aug 14, 2009 at 19:13
  • Sorry,I understand that, I was just wondering why you kept the log file backups past the next full backup. I suppose I can see you might want to point in time recover following a full backup.
    – MrEdmundo
    Aug 17, 2009 at 11:20
  • ..sorry it was I who misunderstood. Keeping a days of logs comes in handy for less used databases that I get calls on Monday from a developer who says he deleted a bunch of rows in a table last Thursday, can I get them back for him. I love developers.
    – SqlACID
    Aug 17, 2009 at 17:54
  • Best not mention I am one then - ;-) Thanks for your help.
    – MrEdmundo
    Aug 18, 2009 at 7:37
1

No. Having daily full backups and log backups every 15 minutes is a valid backup strategy.

Possible down sides include:

  • Using more disk space (if would be helpful to know the backup sizes of each type & your retention rate) because you will have to retain your logs at least till the next full backup.
  • Longer period in restoring all those log backups if you don't use management studio or a script to automate it.

My thoughts: diffs every 2 hours seems a bit like over kill to me, especially if the db's are small and so much of the data is changing that you may as well do a full!

Full and differential backups do hurt performance as the process has to read every page in the database. There is no way I'd be doing full or diffs on a db when its under load or during business hours being accessed by users.

0

slightly off topic, but related to what is said here: "..sorry it was I who misunderstood. Keeping a days of logs comes in handy for less used databases that I get calls on Monday from a developer who says he deleted a bunch of rows in a table last Thursday, can I get them back for him. I love developers."

Question: can't you just do a point in time restore with Thursday night's full backup, or is not not feasible for some reason?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .