lptech (6/6/2013)
Looks like we need to get introduce deduplication to the DBMS world. In the virtual world, VMWare and Hyper V only keep one copy of the same block in memory, and when doing backups for that matter. Granted, we have transaction log and differential backups, but deduplication for database backups should be built into the backup subsystem. So if only 1% of the database blocks have changed, the backup command should only back up those blocks. During restores, ALL of the blocks should be retrieved without DBA intervention.Backupand restore is an area where SQL Server isn't even where DB2 was in V3, back in the early 1990's.
I would agree. Some of the de-dupe products don't work well over time with SQL Server, since those original blocks have to be maintained over time and assembled with the changed ones.
However, I'd think the backup process could be streamlined and at the same time smart enough to produce some "changed blocks" from a base backup, excluding indexes. If we throw those out, because they are re-org'd, then I think lots of data rarely changes.
The thing is, we'd need to rewrite those blocks the same instead of just streaming out pages. After index movement of data, including the clustered indexes, the blocks are not necessarily in the same spot each day when they are streamed out from the datbaase.