I'm in the process of re-writing our maintenance plans and at the moment the focus is backups. Whilst preparing this plan, I'd like to try and ensure that procedures are in place to ensure that backup performance is optimal. I am aware of implementing the below, however I'd like to get an idea of how I can improve beyond that scope.
- Backup Compression
- Multiple data files for larger databases - perhaps over 100GB?
- Writing backups to another I/O subsystem
- Defining values for parameters including BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT
- Purging old files
- Deleting backup history
Beyond the above, is there anything that would be benificial to implement. Also whilst setting the values for BLOCKSIZE, MAXTRANSFERSIZE & BUFFERCOUNT, are there any considerations I should take/how do I go about defining the correct values? I appreciate it will be a bit of trial and error with testing but it would be useful to get an idea of best practices.
To give an idea of my routine, I'm to run hourly transaction log backups, daily differentials and weekly full backups.