Looking for a conclusive answer. Is Instant File intiailzation used during backups?. I know how it will help with MDF file growth. I know it won't help with LDF growth.
Second Question:
If i am doing my backups to a drive on a different system, do i have to grant 'PERFROM VOLUME MAINTENANCE TASKS' to my service account user on the system where the backup is going to land.
Thanks for your time.
Don't know the answer to either of those, sorry, but if backup performance is an issue then turning on Compression will help - of course you may well already be doing that ...
There is a single, central, default setting which will make all backups, by whatever route they are currently being generated, use Compressed, and it hasn't been an Enterprise-only option for a while.
Thanks for trying. we are already using compression.
IFI wouldn't apply to backups because it's not meaningful there, since only the file space that is needed will be to written anyway. That is, for a 6GB backup, only 6GB of disk is allocated and all of it will be written to. File allocations are inherently different, in that they will allocate more space than will be used immediately.
i get it that conceptually, there is no good reason to write the same number of zeros to disk as you are about to fill up with good data. BUT does microsoft do it anyway?
Does microsoft do zero file initialization while creating a backup?
IF so, will IFI fix it?
If so, do you need to grant PERFORM VOLUME MAINTENANCE on the server that will 'receive' the backup in the cases where the
backup is not 'landing' locally.
No, SQL Server does not initialize backup files before writing to them.