When Do You Take a Full Backup?

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , , . Bookmark the permalink.

1 Response to When Do You Take a Full Backup?

  1. Pingback: The Top Ten Skills You Need « Voice of the DBA

Comments are closed.