How Do I Develop a Backup and Restore Strategy?

One of the most critical aspects of administering SQL Server is the Backup and Restore or Disaster Recovery strategy. Frequent questions in this forum indicate a general lack of knowledge and planning for backup and restore operations.

All too often, the question is asked, "How can I get my data back?" Usually, this is asked after a disk crash or a destructive query has been run. When asked if there is a backup, the user may reply, "It's X months old." or "We don't have a backup."

With these problems on my mind, I present the following suggestions. This is not a comprehensive paper on Backup and Restore. There are many of those available on the web and in books about SQL Server Administration. Hopefully, this FAQ will help someone get started with disaster planning.

Specific information included here refers to SQL 2000 but the basic principles can be applied to other versions and even other databases.

Develop a database maintenance plan for each database.

Note that I recommend a plan for each database. You could use the same plan for all databases. That is better than no plan. However, activity, size, number of users, importance and other factors should influence the development of a specific maintenance plan for each database.

How often should you run DBCC database checking? How frequently will you need to reindex tables? When can this maintenance be performed? How frequently should you backup databases and what type of backup(s) should be done? This leads to my main pointà

Develop a disaster recovery plan.

Determine what types of backups are needed to meet your business needs. How long can you afford to have a database down? How long will it take to recover if a disk crashes? How important is transaction recovery? How much data can you afford to lose?

There are lots of other questions you need to answer. Check the following links for Disaster Recovery/Backup and Restore articles.

SQL Admin: Backing Up and Restoring Databases
http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_9zcj.asp

SQL 2000 Operations Guide Ch 4-System Administration:
http://www.microsoft.com/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops4.asp

Database Backup and Recovery
from Chapter 11, Microsoft SQL Server 2000 Administrator's Pocket Consultant by William R. Stanek.
http://www.microsoft.com/technet/prodtechnol/sql/maintain/monitor/11ppcsqa.asp

Knowledgebase: Disaster Recovery Articles for MS SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307775

Backup strategy info at other sites:

DevX:
http://gethelp.devx.com/techtips/thesqlpro/10min/10min1100/10min1100.asp
http://gethelp.devx.com/techtips/thesqlpro/10min/10min0101/10min0101.asp

About.com:
http://databases.about.com/library/weekly/aa031101a.htm
http://databases.about.com/library/weekly/aa031101b.htm
http://databases.about.com/library/weekly/aa031101c.htm

SQL Server administration best practices
by Divya Kalra & Narayana Vyas Kondreddi:
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm

Continuing Administration and Monitoring

Once the Backup and Restore strategy is in place, administration becomes easier but doesn't end. Regular backups will maintain database and log file sizes within reasonable limits. You still must monitor database and logs in case situations change. Backup schedules can be adjusted as needed.

Our general plan is to do full backups daily, transaction backup every 15, 30 or 60 minutes depending on the databases. We perform differential backups in a few databases. We are currently reviewing our backup and restore procedures and will be implementing changes based on our changing environment and requirements.

I hope this information helps. Please fell free to comment and make suggestions.

Terry Broadbent
http://tlbroadbent.home.attbi.com/index.htm