Question 1: Is there a commercial backup product that will give a similar backup size to stripping non-essential data like indexes out of the database?
No. There's a lot of backup compression products out there (Quest LiteSpeed, Red Gate SQL Backup, Idera SQLSafe, Hyperbac, etc) but all of them function by just compressing the output of SQL Server's regular backup process. Some of them do it in tricky ways - HyperBac and LiteSpeed's Engine option are file system filter drivers, meaning they're intercepting the output on its way to disk - but the end result of all of these products is just compressed backup output.
Question 2. Is there a comprehensive script out there to dump all this extra data?
Over time, as you keep more history in the database (4, 5, 8, 10 years) you won't want to rip out all the index data and rebuild it on the other side of the WAN. Instead, you want to just transfer the modified data, and that's where log shipping comes in.
You shouldn't do this.
But if you really, really wanna do this (and no, I won't help you), you can do it with filegroup backups. Set up your database filegroups like this:
- Primary filegroup (required, but leave it empty)
- ClusteredIndex filegroup (put your clustered indexes here)
- ExtraneousCrap Filegroup (put everything else here)
Start doing compressed filegroup backups of just the first two, and copy those smaller ones to your DR server. You can use SQL Server 2008's filegroup backup and restore capability to just restore the Primary and ClusteredIndex filegroups, and then they'll immediately be available for querying. They're not really going to be workable until you get that ExtraneousCrap filegroup online, but there's a nasty trick for that too - in the MVP Deep Dives book, there's a chapter on editing the system tables in order to make the ExtraneousCrap filegroup and all of the associated indexes disappear. This trick is dangerous, totally unsupported, and a hell of a bad idea - but hey, you asked for it.