Timeline for Huge Size (still) with Differential Backup
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 31, 2017 at 14:16 | history | edited | David Spillett | CC BY-SA 3.0 |
Added ideas for mitigating problem of backup size being transferred
|
| Jul 31, 2017 at 13:57 | comment | added | David Spillett | @ChrisLumnah - the network (and storage) load of log shipping, mirroring, AGs and so forth from one instance of the warehouse to another is going to be of the same magnitude as using differential backups, it will still need to copy most of the data from the source to the copy. Depending on the input data size & locations and the amount of validation & transformation work done, performing the ETL in multiple locations may be more efficient particularly as the index pages will not need to be transferred. | |
| Jul 31, 2017 at 12:26 | comment | added | user103243 | @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 | |
| Jul 30, 2017 at 20:34 | history | edited | David Spillett | CC BY-SA 3.0 |
Punctuation
|
| Jul 30, 2017 at 18:16 | history | edited | Sean Gallardy | CC BY-SA 3.0 |
diff bitmap tracks extents, not pages. changed pages to extents.
|
| Jul 30, 2017 at 14:35 | comment | added | iKnowNothing |
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
|
|
| Jul 30, 2017 at 13:43 | comment | added | Erik Reasonable Rates Darling | @DanGuzman yeah, it probably won't help with backup size, but David also gave general advice that I thought it would fit into. | |
| Jul 30, 2017 at 13:31 | comment | added | Dan Guzman | @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. | |
| Jul 30, 2017 at 13:24 | comment | added | Erik Reasonable Rates Darling | 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. | |
| Jul 30, 2017 at 13:14 | history | answered | David Spillett | CC BY-SA 3.0 |