When is a Full Backup Taken?
Most people might answer this with “every day”, or “as often as needed”, but those are a little nebulous, and not necessarily correct.
I do recommend that you backup as often as you can, which is every day for most people. Those with large databases might go once a week, or even once a month, but you want to get a full backup periodically. If you have a really large database, say 50TB or greater (in 2011), you might opt for a yearly SAN snapshot of some kind, but you need some full backup as a base.
So when do you backup your database? Here’s a list of some times you might think about:
- As often as you can on a regular schedule (daily, weekly, monthly, etc)
- Before applying a Service Pack or CU*
- Before applying a Windows patch*
- Before upgrading your application*
- Before a large data load*
- After restoring your instance in a DR situation
That last one might throw people, but I’ve seen more than a couple situations where someone recovered a database in a DR situation and never setup backups. And there was another failure.
Whenever you create a database, either from scratch or after a restore, make a backup.
* Note that if you have Enterprise Edition or above, you can use Database Snapshots to substitute for a full backup in some of these situations.