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

I dont think you are paraniod by running backups every 3 hours. Basically, your backup plan has to be measured by your restore requirements. How long can you afford to be down while you recover vs how much data are you willing to lose before you are down. For SQL Server, you can shrink the amount of data you are willing to lose considerably by adding transaction log backups to your backup plan. Many people do these every few minutes depending on the amount of transactions going through the system. To do a restore you just restore the last full, the last increment and then all the transaction log backups since the incremental. This can give you a minimum loss of data but it can take a little whle to apply all the transaction log backups. I see the following pretty regularly: Full backups - Weekly Incremental backups - Nightly Log backups - every few minutes depending on requirements (might be good once an hour etc)

Answer 3

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.

Answer 4

Remember to do firedrills where you actually try to restore from the backups you create (to a test-system). This should be done perhaps once a month.

Answer 5

The minimum I recommend to my customers is to do a nightly full backup of the database and then a transaction backup every 3 hours. It always amazes me how many people never setup a backup. Those are always bad calls.