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!

  • 2
    Its highly unlikely for log backup to cause performance issue Yes backup does cause some load depending on backup size but I have not seen issues caused by trn log backup. Plus you run at risk not having point in time recovery are you ok with it ? What is PO and RTO ? Changes dont trigger diff backup only user executes diff backup which backs up recent extents changed – Shanky Aug 18 '14 at 18:50
  • 2
    Backups should be geared toward your recovery objectives. If this system were to be rebuilt, what level of data loss would be acceptable to the business? Would they expect the system to be consistent as of the most-recent data load? – nabrond Aug 18 '14 at 18:51
  • 2
    Do you have any evidence whatsoever that transaction logs timed to meet your recovery objectives actually do have an impact on your load performance? I mean if your business is ok with only recovering to a single point in time for yesterday if the server crashes anytime before the diff backup today, go nuts, but I'm going to suspect that most stakeholders aren't going to be okay with that. – Aaron Bertrand Aug 18 '14 at 19:09
  • 2
    You have to be careful when relying on differential backups. Any adhoc fullbackup taken without using with COPY_ONLY will break the diffs. Brent talks about things to consider when relying on Differential backups - Backups Gone Bad: The Danger of Differentials – Kin Shah Aug 18 '14 at 20:23

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.