From a recent email:
What, in your opinion, is the best recovery model for a GP database? Full? Bulk_Logged? Simple? They all seem to have their pros & cons but I can't find anyplace where someone says this model is better than the other two because of X, Y or Z reasons.
A:
Simple
If you use Simple, transaction logging is disabled. Simply backing up the database will truncate the transaction logs. (If the logs are already too big, run this script http://vstoolsforum.com/blogs/sqlserver/archive/2007/01/11/compact-sql-logs.aspx). So, if the client is OK with their restore point being last night, when the backups ran, then Simple is fine. It's easier to maintain.
Bulk_Logged
I've never used Bulk_Logged, so I can't speak to that.
Full
Usually the client will want to be able to restore a crashed server up to the last hour, so we use Full. Using Full, the act of running a transaction log backup is what truncates the log. So, you're running a backup every evening and running transaction log backups every hour.
Some of the system databases don't support Full, so I usually have two Maintenance Plans - one for the system dbs and one for the user dbs. I never use the 'these specific databases' option because the next time that a database is added to the server, you'll forget to add it to the backup. Choosing the 'all user dababases' option stops that worry.