patternsqlMinor
Optimized Way of Scheduling a Differential Backup
Viewed 0 times
schedulingwayoptimizeddifferentialbackup
Problem
I am working with a data warehouse with SQL Server 2012 and was wondering what would be the most optimized, AUTOMATED procedure for a backup/restore strategy.
Current observations and limitations:
1) Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions
2) Current plan is to do full backup every week and differential backup every day
I am not sure when DML operations will happen as it depends on my application's usage, but is there a way to just track the NUMBER of changes to a database that would trigger a differential backup? A way that would not affect performance? I do not want to be taking unnecessary differential backups.
Would Change tracking be a good solution for my scenario? Or would there be overhead involved? I do not need to know the actual data that was changed, just the fact that it was changed by a certain amount.
Thanks in advance!
Current observations and limitations:
1) Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions
2) Current plan is to do full backup every week and differential backup every day
I am not sure when DML operations will happen as it depends on my application's usage, but is there a way to just track the NUMBER of changes to a database that would trigger a differential backup? A way that would not affect performance? I do not want to be taking unnecessary differential backups.
Would Change tracking be a good solution for my scenario? Or would there be overhead involved? I do not need to know the actual data that was changed, just the fact that it was changed by a certain amount.
Thanks in advance!
Solution
First, this assumption is questionable:
"Cannot use transaction logs as it would affect my load performance -
datasets are potentially huge with large transactions"
When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations are truly important to you, then you need to look at using minimally logged operations like bulk inserts. This is way harder than it sounds - check out the SQL 2008 data loading guide for more details. (For example, you may not be able to do it on a table that already has multiple nonclustered indexes and data in it.)
If it really is a good fit, though, check out this Serverfault post on estimating the size of your differential backup.
"Cannot use transaction logs as it would affect my load performance -
datasets are potentially huge with large transactions"
When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations are truly important to you, then you need to look at using minimally logged operations like bulk inserts. This is way harder than it sounds - check out the SQL 2008 data loading guide for more details. (For example, you may not be able to do it on a table that already has multiple nonclustered indexes and data in it.)
If it really is a good fit, though, check out this Serverfault post on estimating the size of your differential backup.
Context
StackExchange Database Administrators Q#74287, answer score: 4
Revisions (0)
No revisions yet.