Home / server / Questions / 54230
Accepted
MrEdmundo
Asked: 2009-08-15 06:03:34 +0800 CST

SQL Server do I need my differential backups?

  • 772

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

sql-server

3 Answers

  1. Best Answer

    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.

    • 3

  2. 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.

    • 1

  3. 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?

    • 0