You are certainly welcome.
Here is how recovery model and backups work:
If you are in SIMPLE recovery model the transaction log gets truncated occasionally (when is complicated and not germane to this discussion) and thus does not grow. You do not get point-in-time recovery with the SIMPLE model.
If you are in FULL recovery model the transaction log will get truncated during the log backup if a full backup has been done. FULL recovery model, when combined with transaction log backups will provide point-in-time recovery. FULL recovery model, when *not* combined with log backups will provide an ever growing transaction log file.
I really do not see the need for point-in-time recovery for PLANNER databases. Honestly, I don't really see much need for any recovery for them. They are really nothing more than applied ESU's and easily rebuilt by re-applying the ESU's. Again, your choice though as you weigh the cost/benefit.
Some random related notes:
-Truncation does not reduce the size of a physical log file, it reduces the size of the logical
log file.
-If one does a full backup, a transaction log backup, and a DBCC Shrinkfile on the transaction logs, the physical files will be shrunk.
-Shrinking files (database or transaction log) is not recommended for files that will re-grow
-Full database backups alone do not truncate transaction logs.
-Using full recovery model without also doing transaction log backups or specifying that the transaction log is truncated during a full backup (SQL EM or NetBackup) will result in the transaction logs never being truncated.
About the MDF and LDF files, the MDF (or NDF for secondary) files are data files and the LDF files are transaction log files. MDF (NDF) and LDF are standard extensions but you could actually specify any name for the data and transaction log files. We are not really doing filesystem backups so the full (and/or differential) and transaction log backups do not directly relate to backing up those files.
Regarding file sizes, you are absolutely correct on the max file sizes for MSDE and SSE. I apologize for my misguided answers about those.
[ QUOTE ]
Thanks again Jeff for your explaination. Pardon me for this but I am not a SQL guy so as I understand correctly changing the recovery model from FULL to Simple would mean that the restoration cannot be point in time. Is it? But the DB can still be restored?
Also changing it to FULL means the log file increases is size more as compared to SIMPLE? and hence a transactional log backup strategy needs to be in place if the recovery model is FULL?
Finally as I said does this need to be only for the transactional log files i.e. ldf files or also for the data file i.e. mdf files?
Also I too agree to your point of having unlimited growth but I wasn't too sure if there are internal limits to that. I read somewhere MSDE had a 2 GB upper limit whereas SSE has 4 GB.
Thanks,
CNC Guy
[/ QUOTE ]