×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming > FAQs

Microsoft SQL Server: Programming FAQ

Restoring SQL Database

How Do I Develop a Backup and Restore Strategy? by tlbroadbent
faq183-1784
Posted: 13 Apr 02 (Edited 26 Jun 02)


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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close