June 10, 2014 at 7:31 am
Hi - I currently have a maintenance plan setup which runs a full backup nightly and then trn logs from 06:30 - 19:00 every 30mins. I want to now incorporate Differential backups to reduce the amount of time to recover the DB, as well as reduce space on the Server. Can someone advise me of the steps needed to achieve this please.
Kind Regards
Dax
June 10, 2014 at 7:49 am
When do you want to run the diffs?
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, 2014 at 7:55 am
Hi Gail - I'm thinking of running a full backup nightly, Diff's every 4 hours with TRN logs in between. Does that sound a feasible approach?
Kind Regards
Dax
June 10, 2014 at 8:04 am
Whether it's feasible or not depends on whether it meets your RTO and RPO requirements. I can't answer that.
Adding diffs every 4 hours to your current strategy will reduce the time needed to restore. It will also *increase* the space required for your backups.
All you need to do is add another backup job like your full database backup, schedule it every 4 hours and just add the clause WITH DIFFERENTIAL to the backup statement.
eg
BACKUP DATABASE MyCriticalDB TO DISK = 'X:\Somewhere\MyCriticalDB.bak' WITH DIFFERENTIAL
Or if you're using maintenance plans you just select 'Differential' from the Backup Type dropdown.
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, 2014 at 8:06 am
Hi Gail - Many thanks for the reply.
Kind Regards
Dax
June 10, 2014 at 8:06 am
It sounds like a good approach, but, one point. You do know that it's actually not likely to speed up your restore process. Assuming you script your restores, currently you have a full restore to run, then x number of log restores. Where as, with differentials, you'll have the full restore, a differential restore, and then x number of log restores. The actual restore time is likely to go up, at least in my experience. That's not to say what you're proposing is a bad idea. In general, I tend to look at differential backups as a way to reduce the time and load of the backup process itself, not the restore process. For example, I'd rather run a full backup on Sunday and then differentials each day so that only the Sunday backup puts the full load of the backup process on the server. Then the differentials during the week, each one getting bigger. Other than that, I keep running log backups every X amount of time (anywhere from 2 minutes to 1/2 hour depending on the database and the business requirements there).
The one thing you'll want to do with your process is keep running the log backups, even if you're running the differential. You want the recoverability that the log backups offer and it's independent of the differentials, giving you added protection.
"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, 2014 at 9:58 am
For databases with a significant number of transactions, I would expect differential backups to speed up the restores somewhat. My reasoning is that applying tran logs requires that every individual transaction be reapplied, whereas applying a differential requires only that all changed blocks be copied into a specific, pre-determined location.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply