What is the best practice for backing up a database in Simple mode?

Question

I am trying to create a SQL Job for backing up my database. I want to have:

1. A weekly full backup
2. A daily differential backup
3. Something that will clean up backups that are older that 14 days

I started doing this with the Maintenance Plan, but decided managing it through jobs would be more flexible.

My questions are: how do I name the backup files? Do I name them with a date naming convention and use the same convention to delete old files? Is there an easier way?

If I'm over thinking this and should be using a Maintenance Plan, I would appreciate that answer as well.

Answer 1

I'd recommend Ola's scripts for SQL Server maintenance - backups, re-indexing, checkDB, house-keeping, everything! It takes care of naming conventions too! They are very clever, and award winning too!!!! :)

Personally, if your databases are small, I'd keep to a full daily backup schedule. I believe Microsoft recommend that as well - simplicity is best approach.

Ola's scripts: http://ola.hallengren.com/