Difference between restoring DB & restoring server?

[pkelly_sts]

If you had a SQL server with reasonably sized DBs, (lets say 200Gb as an example) and were in the habit of taking a pre-change quick backup immediately prior to any updates to SQL-based apps, then have to do a full DB restore, how much of the original VM info is de-duped during the next backup cycle?

If i were to do a full SQL restore, would I end up with 200Gb of changed blocks needing copying across the network in a backup copy job, or is Veeam clever enough to spot the existing blocks & heavily de-dupe it?

If not, and I do a "full VM restore" instead but choose the quick restore which only restores changed blocks, I can't remember if this initiates a full backup of the VM due to change of identifier etc.?

I'm just working on our SQL restore processes & want to ensure I'm aware of all options/gotchas before we actually /need/ to do a restore...

(I've pointed out to the DB users/admins that we actually have the facility to restore to a transaction level but they're of the thought-process that if they ever need a restore, they'd simply prefer to restore the DB to the last-backup state...

[sg_sc]

vSphere will see 200GB of changed blocks and so Veeam will back up 200GB of data from source, however the in-backup-file deduplication should see the duplicate blocks and make sure the incremental is nog 200GB big. That's the theory as I understand it, maybe you can do a test on a staging server?

[pkelly_sts]

Yeah kind of how I thought it would be. I may see if I can have a play with a dev/duplicate server...

[skrause]

We do a full database refresh/restore on some testing environments with large SQL databases (300GB) relatively often here and the next Veeam incrementals tend to be pretty tame, considering. As long as you don't delete the databases before the restore, chances are the blocks being over-written by the SQL restore will be the existing blocks. Looking at the report on one such environment, a typical night sees about 300GB read/100GB transferred for an incremental, on the "refresh" nights it is about 1TB read/300GB transferred.

You also need to remember that Veeam's deduplication only covers individual backup files, not entire chains. So Veeam's de-duplication will have almost no impact on your file size between one incremental and another. In terms of file-size reduction performance on SQL server, Veeam's compression is where most of the savings will likely come out as SQL, as a general rule, is highly compressible.

[pkelly_sts]

Thanks for your insight, most useful.

[foggy]

If not, and I do a "full VM restore" instead but choose the quick restore which only restores changed blocks, I can't remember if this initiates a full backup of the VM due to change of identifier etc.?

VM ID does not change during Quick Rollback, since it is a restore to original location.

[pkelly_sts]

So no major impact on backups but presumably resets CBT so requires a full read at least?

[foggy]

Correct.

[pkelly_sts]

Thanks