What is your SQL Server Backup/Maintenance Plan?

Question

As a followup to this question I'm wondering what your SQL Server backup/maintenance plan is, and how I can make mine better.

Currently I'm running two plain-jane maintenance plans from the plan wizard.

The first runs nightly and does just about everything ...

• Full database and transaction log backup
• integrity check, rebuild index, recompute statistics, etc. (I checked everything except incremental backup)

The other runs every three hours and does an incremental backup (I'm paranoid, I know this is probably overkill).

Backups are to disk, full backups are sent to the SAN, retained for a week.

What do you think, is this a sensible plan? Any suggestions?

EDIT: This is SQL Server 2005. DB is 5GB, grows about 1GB/month.

Answer 1

Sounds pretty good. I'm more paranoid. I do two daily full backups and hourly transaction log backups. Depends on the size of the database or course. The backups are done direct to disk and then backed up to tape nightly.

You probably don't need to do the maintenance tasks every day. I only do them on the weekend, except for this one table which we reindex every night. Again this depends on the size and activity of the database.

If you got enough cpu and disk space you can zip the disk backups to save space and make transferring to tape or other location quicker.

Answer 2

You should be speaking with your users/customers/data custodians - whatever you call them. They need to have a clear understanding of how much work they can lose. Write up an SLA if you don't have one. You don't want any surprises when it comes to bad news.

They also need to understand that restoring takes time. You need to plan your recovery plan to create an acceptable restore time. This may mean a Daily full backup, 4 differentials and log backups every 5 minutes. This is not crazy or paranoid as Marcus Erickson said - it all comes down to your information and the dollar value your organization puts on it.