INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...This site is truly a marvel. Without a doubt the most comprehensive, friendly and just plain useful resource of its kind..."
Where in the world do Tek-Tips members come from?
Restoring SQL Database
How Do I Develop a Backup and Restore Strategy?
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
SQL 2000 Operations Guide Ch 4-System Administration:
Database Backup and Recovery
from Chapter 11, Microsoft SQL Server 2000 Administrator's Pocket Consultant by William R. Stanek.
Knowledgebase: Disaster Recovery Articles for MS SQL Server
Backup strategy info at other sites:
SQL Server administration best practices
by Divya Kalra & Narayana Vyas Kondreddi:
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.
Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
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:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close