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.)
There are lot of things I would like to say
What I'm looking for is some suggestions or 'gotchas' that may arise from switching some to the FULL recovery model.
As far as I know if you change recovery model of database during maintenance window time or when load is relatively less there wont be any problem. It wont create a situation.
I'm also aware that as soon as we switch to FULL recovery that I need to run a full backup on that database. (I understand that FULL mode won't be truly enabled until we do this).
You can take differential backup also perhaps this would be very helpful if you have big database. anything that links LSN chains would do.See below link it has good information about switching between recovery models
http://msdn.microsoft.com/en-gb/library/ms189272.aspx
My plan is also to weekly run a full backup, wipe the log files from the previous week and, essentially 'start over'.
Stop there, this is not correct approach just because you did a full backup and log backup please don't be in idea that you can safely remove old log files and full backup. Do you have a plan to test old backup by restoring to actually see that in case of disaster this backup files would work. Remember only successful restore guarantees that your backup is totally consistent. Do you have a option to check backup integrity.If not please include it into your backup plan. At least keep 4 day old backup files( this is what I do on local disk) before if you delete backups This strategy is upto you keep backups for time where you are sure they wont be useless if disaster strikes. Sometimes business want only specific data change to which was made couple of days. I also have my database backup up on tape and that tape is stored for 6 months.
(maybe switch to BULK_LOGGED for that time period as to not explode the log file?)
Changing recovery model to bulk logged and doing bulk logged operation would make you loose point in time(PIT) recovery so if you are concerned about PIT recovery dont do that. Instead rebuild index through intelligent script which only rebuild fragmented index like one Is Ola Hallengren index rebuild solution please note that if Index is rebuild with full scan for that index stats is already updated with rebuild process.
If you do heavy DML please break it into batches so as not to explode log files.
Best Answer
Setting a database to full recovery is not enough by itself to ensure you have point in time recoverability. You also need to take regular log backups. You should also be taking time regularly to test that you personally know how to execute the required commands to actually perform a restore to an arbitrary point-in-time.
In lieu of running loads of home brewed full backups, the fire-and-forget solution you want is probably to just install Ola Hallengren's backup solution. Running this script installs the required objects and deploys the required jobs to manage all of the full recovery databases on an instance. You will find these jobs 1 under SQL Server Agent > Jobs named by default
DatabaseBackup - USER_DATABASES ...and similar. Add a schedule to these jobs that suits you 2 and disable other backup jobs to prevent conflicts.You should still need to regularly take time in the normal course of business to ensure you and others on your team are able to actually restore the backups you have, regardless of how you take them.
1: assuming you're using SSMS here
2: 5 times daily if you like, but probably a bit less is fine