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.)
Differential backups include data that has changed since the last full backup (ignoring any full backups taken with the COPY_ONLY option).
If you take a differential backup immediately after a full backup, it will be very small, as little (or no) data will have changed. As time goes on, the differential backups will become larger and larger, until you do another full backup, which resets the differential change map.
Note that you must still have the last full backup taken prior to the differential backup, or the differential backup will be useless.
Best Answer
If you are backing up with
NOINITthe backup files will continue to grow if they have the same name and destination.https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017
Personally I always use
INITand a distinct file name (and/or backup the file that is overwritten before it is overwritten)If you are taking hourly differential backups, you might want to consider changing to full recovery and doing hourly t-log backups. But I understand this is not always an option.
You don't have a good description of how you are protecting the full and differential backups, but you unless you have a good reason not to, you probably want each diff and full to use
INITand have date/time stamp in the file name.If you must append your diffs for more than one day, you should probably validate you can restore. I have not tried it, but I suspect that restoring from appended diff is going to be painful at best.
Note that per this answer you can not use
EXPIREDATE(backup expiration date) to keep rolling backups in the same file.