0

I am new to differential backup so please bear with me.

I have a database which the full backup size is ~55GB. I perform ETL nightly on this database. The ETL process involves UPDATE, DELETE, and INDEX DISABLE/REBUILD operations. 1st differential backup produced 48GB backup file!

Now, I know that this is most likely (if not 100%) caused by the index rebuilds (and they are on big tables too!). As index rebuilding is a pretty common technique used during ETL process, what option(s) do I have to minimise the size of differential backup on a data warehouse database?

1
  • The only way to minimize the size of your Differential (without changing your ETL process) would be to take a full backup after the ETL job completes. If the speed of the full backups is a sticking point, then you might try backing up to multiple files (I've seen dramatic improvements in full backup performance by doing, up to 75% reduction in total time). Commented Jul 31, 2017 at 12:27

1 Answer 1

3

That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around that because it must contain every extent that has changed since the last full or differential backup.

If you are not already doing so add the COMPRESSION option to your backups, which can result in considerable savings (and speed up the backup process too by reducing IO).

Also if the size really is a massive issue, this might be one of the few occasions to question if a backup is needed particularly regularly. If you are regenerating all of the data in your warehouse database every day from other sources then you might not need to worry as much about backing it up as often as you do the source data, assuming of course that the source databases have good backup taking and testing processes in place, because you can reconstruct that data from the other sources (or backups thereof) anyway if disaster strikes. Of course this reconstruction process could be significantly slower than restoring a backup, so consult you users and your service restore time SLAs before changing the warehouse's backup plan.

Possible Remediation:

Two possible options to reduce the amount of data that you need to transfer spring to mind:

  1. After each update, make a copy of the data without any non-clustered indexes (backup, restore locally, drop all non-clustered indexes, backup again and transfer this version) and recreate the indexes after restoring at the other end. This avoids transferring all the index data.

  2. Make sure the daily changes are as minimal as possible by only INSERTing new rows and UPDATEing those that need it (leaving identical rows untouched), not rebuilding any indexes (this will mean not disabling them so may slow down the ETL process).
    Depending on the complexity of your ETL process refactoring it this way may be a massive job. One way around this may be to add an extra stage to the end of the process: keep a second copy of the warehouse, rebuild the first using the normal process, then update the second using one MERGE statement per table to INSERT/UPDATE/DELETE as needed being very careful to leave any unchanged rows alone. Then take your differential backups from this DB. Keep minimal indexes in the primary instance of the copy, just keep the primary keys and clustered indexes (which you will want for efficiency of the MERGE operations) and rebuild the rest after restore.
    This reduces the amount of no-op changes, so will hopefully markedly reduce the size of each differential, and reduce space in the backups devoted to index data.

Both these methods try to reduce the backup size that you are transferring at the expense of adding a fair chunk of extra processing at the receiving end (rebuilding indexes after restore) and sending end (maintaining the second copy at that location) so you are trading one bottleneck for another pair which may or may not be practical.

Depending on your data sources and the complexity of the ETL process you may have better luck with building the warehouse DB at both locations each time, as Chris suggested in the comments.

6
  • 1
    I'd add that since there are some operations that can be bulk/minimally logged, (technet.microsoft.com/en-us/library/…), one idea may be to review steps to see if any of them can be changed to ones that qualify. Commented Jul 30, 2017 at 13:24
  • @sp_BlitzErik, although minimal logging will reduce log backup size, I don't think that will change the size of differentials. Modified pages/extents will still need to be backed up regardless.
    – Dan Guzman
    Commented Jul 30, 2017 at 13:31
  • @DanGuzman yeah, it probably won't help with backup size, but David also gave general advice that I thought it would fit into. Commented Jul 30, 2017 at 13:43
  • Thanks all. I've already used COMPRESSION. The ETL does not delete all the existing data but only UPDATE/DELETE/INSERT data from up to a month ago, so bit surprised with minimal reduction on the diff backup vs full. Must be the INDEX REBUILD then? Unfortunately, as part of ETL, I need to do the backup every night because it needs to be restored on another instance/server. This is the main reason why I am looking at differential backup to save size hence time to transfer the backup file across the network. Is there anything else I can try to help me with this? TIA Commented Jul 30, 2017 at 14:35
  • @iKnowNothing if the goal of the backup is to just move data to another instance, instead of backup/restore, you could use log shipping, mirroring, availability groups (if on 2012 and later) or SAN snapshots. Additionally, you could load the data in both systems
    – user103243
    Commented Jul 31, 2017 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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