June 10, 2011 at 9:59 am
Generally we take a full backup of our databases nightly. We now have some datawarehouse databases that are quite large so that approach is not practical.
I know that Sql server (and tools such as RedGate) offer differential backup of databases. That will help with backup size because the differential backups are smaller than a full backup but what is the impact of that approach on CPU utilization? (SQL has to determine what has changed in the database from the last full backup. Seems like we also need to store all trans logs since the last full backup which will chew up more space than we presently do).
TIA,
Barkingdog
June 10, 2011 at 11:00 am
I have not seen a differential backup cause any more pain in terms of CPU than a regular backup. I suppose it might add something to the load, but from everything I've seen it's probably a very negligible value.
As to keeping logs, once you have differential backups in place, you only need logs back to the last differential. Recovery would go, Full Backup, Latest Differential Backup, Logs up to the point in time you're aiming for. So no, you don't need to keep extra log files laying about.
Understand, each differential backup is bigger than the last. Depending on how volatile your data is, you could see differential backups that are near to or exactly the same size as the full backup. In terms of storage this is just a bit of a pain. In terms of recovery this could be a huge issue since you're effectively restoring your database twice. So if you decide to use them, plan it it out carefully.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2011 at 11:05 am
A differential backup only holds the "information" (data, DDL, sprocs and the like) that changed since the last full or file backup. "Changed" basically means that the related transaction completed successfully. In order to use/restore a differential backup you'll need the corresponding full/file backup and all intermediate differential backups.
For details, please see BOL, section "differential backup" or search this site or use google.
Regarding the transaction logs to keep: technically, all you'd need for a "most recent" or a "point in time" recovery is the last full/file backup, a differential backup after that and all transaction log backups after the related differential backup.
However, if the last full/file backup cannot be restored for whatever reason, all differential backups and transaction logs based on that are pretty much useless. Unless you have a previous full/file backup and Edit:[all one] differential backup taken after that and all transaction logs after the Edit:[last available] differential backup (the one before the full backup that failed). How far you need to be able to go back depends on the business requirements. You should also frequently restore the backup files on a test system to verify they are useable. It's also a good practice to be prepared for the emergency...
I'm sure Gail and some of the others will have a more detailed answer...;-)
June 10, 2011 at 11:08 am
Grant Fritchey (6/10/2011)
As to keeping logs, once you have differential backups in place, you only need logs back to the last differential. Recovery would go, Full Backup, Latest Differential Backup, Logs up to the point in time you're aiming for. So no, you don't need to keep extra log files laying about.
You don't need to, but you probably should. If that last diff is damaged somehow and won't restore and you don't have the log backups from before that, your best restore is to the full backup. That could be a lot of lost data.
What I prefer is log backups going back two full backups. That way there are multiple valid restore options should a backup be damaged.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2011 at 11:11 am
GilaMonster (6/10/2011)
Grant Fritchey (6/10/2011)
As to keeping logs, once you have differential backups in place, you only need logs back to the last differential. Recovery would go, Full Backup, Latest Differential Backup, Logs up to the point in time you're aiming for. So no, you don't need to keep extra log files laying about.You don't need to, but you probably should. If that last diff is damaged somehow and won't restore and you don't have the log backups from before that, your best restore is to the full backup. That could be a lot of lost data.
What I prefer is log backups going back two full backups. That way there are multiple valid restore options should a backup be damaged.
OK. Your level of paranoia just exceeded mine. You sure you're not a DBA?
Did I mention that I would also test all backups, and by test I mean, restore them. It's the only way to know for sure that they're sound. Still not as paranoid as you though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 10, 2011 at 11:46 am
Grant Fritchey (6/10/2011)
OK. Your level of paranoia just exceeded mine. You sure you're not a DBA?
Lol. No, not a DBA, but I do tell people how to do DBA work these days...
This was habit at the bank. Losing 3 days of investment data because the diff was bad could be considered a career-limiting move. We didn't test restores, didn't have the space (no matter how much we begged). 1TB database, fulls on sundays, diffs daily. We kept 2 weeks of backups + month end full for 14 months (on disk) and month-end for 7 years on tape.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply