MySQL – Running Backups to Network Location

The first thing I generally recommend doing is introducing differential backups to the backup strategy. If the amount of data churn is moderate or low, this can work extremely well for saving space and time because you only back up changes rather than everything. This does add some complexity to restore scenarios, and you must make sure you know what COPY_ONLY backups are.

The second thing is leveraging native backup compression. SQL Server 2008 Enterprise and SQL Server 2008 R2 Standard editions support backup compression out of the box -- no need for 3rd-party tools. If you aren't taking advantage of this, you're really missing out. It is off by default for backwards compatibility. You can turn it on server-wide by default in the Server Properties | Database Settings page, or per-backup using the WITH COMPRESSION option. This will still have a small CPU hit, but the cost will be amortized over the duration of the backup instead of all at once (assuming backups are I/O bound, which they usually are).

Some other random thoughts that may or may not apply to your environment: stagger the backups to reduce I/O contention; back up to multiple locations simultaneously; back up to LUN(s) dedicated to backups; tweak the backup buffer settings.

For the 2005 backups, what you can consider doing is limiting the amount of CPU used by the compression utility by using Windows Server Resource Manager. Or, since the file has to be written somewhere before that step happens anyways, you could offload that process to another box.

Encrypting backups is not something I'm too familiar with, but it will depend on how you plan to encrypt the data and backups. If you use Transparent Data Encryption in SQL Server, you'll likely want to turn off backup compression because there won't be anything to gain because of the randomness of the encryption. If you use a 3rd-party encryption strategy, see the previous point about using WSRM or offloading the process to a different box, because encryption will also require CPU.

Seeing that the recovery model is set to simple and msdn states that the simple recovery model does not support point-in-time recovery - Does this mean that I won't be able to use my transaction log backups to restore the database in a disaster to an hour before it happened?

Even taking a transaction log backup is not supported for databases using the SIMPLE recovery model. This is a restriction of the database engine based on how this recovery model works, and the recovery features it doesn't support, as you mentioned.

A transaction log backup maintenance plan task automatically skips databases in SIMPLE recovery to avoid causing errors.

Which backup should be done first, the database backup or the transaction log backups? Articles that I'm busy reading say I should do the database backup first and then the transaction log backup else I will get maintenance plan errors, but I'm currently first backing up my transaction logs and then data databases and I'm not getting any errors.

For the reasons I mentioned above, it won't matter for databases using SIMPLE recovery, as they will be skipped by the transaction log backup task.

For databases in the other two recovery models, a full backup must exist before you start taking transaction log backups (just the first time), or you will get an error -- this is probably what the articles refer to.

Point-in-time recovery ability is normally driven by business need -- in other words, you determine how critical the data is and how much you can afford to lose, then set the appropriate recovery model to meet those needs, and finally create a backup solution.

Even though SIMPLE recovery does not support point-in-time recovery, if an hour of data loss is okay, perhaps a differential backup solution could work for you. (There are far too many variables that go into developing this kind of solution to give you a complete picture with what was provided in the question.)