• I use the following strategy : At full backup time, two new file name are generated (including a timestamp in the name): one for full backups, one for log backups. Full backup is done to this new file, and log backup initialize log backup file with a first log backup. All other logs backups are appended to this file all the day.

    To apply this discipline automatically, I put on SourceForge a free solution (YourSqlDba) for maintenance and backups. to do maintenance, optimizations and backups. It is all T-SQL Script based, and articulate around Sql Agent and database mail mechanisms. This solution has its own database and T-SQL code, with a detailed logging system for maintenance instructions and an email system that let you know when things go well as well than when things goes wrong.

    By mailing if something goes wrong (ex: when a backup file can't be written), DBA knows that backups needs to be taken now again.

    It is important not to mix complete and log backup into the same backup file, because of the risk of overwriting backup content.

    This system allow to have many log backups to help recovery from a previous complete backup. By default only the complete backups of the day are kept, and week log backups. These files all bears different name, so it avoids accidental overwrite when copied. Full backup must be copied each day to another media to avoid filling local backup space. This is quite easy to do this by adding a second step to the sql agent's job that does the backup, especially for that sole purpose.