Reliability Solutions for the Aerospace Industry
Powerful solutions for high-performing components

Reliability Solutions for the Airline Industry
Reliability solutions that make your profits soar

Reliability Solutions for the Automotive Industry
Reliability from R&D to the road

Reliability Solutions for the Chemical and Process Industry
Build processes with reliable results

Reliability Solutions for the Defense Industry
A strong reliability program breeds strong defense

Reliability Solutions for the Electronics and Appliances Industry
Reliable components ensure reliable business

Reliability Solutions for the Energy Industry
Let reliability light the way

Reliability Solutions for the IT Hardware
Reliability keeps the world connected

Reliability Solutions for the Medical and Healthcare Industry
Reliability for life

Reliability Solutions for Military Organizations
Dependability begins with reliability

Reliability Solutions for the Oil and Gas Industry
Streamline processes along the pipeline

Reliability Solutions for the Semiconductor Industry
Big results for small materials

Reliability Solutions for the Telecommunications Industry
Reliable solutions to better help you connect the world

Reliability Solutions for the Transportation Industry
Results that get you where you need to be

Reliability Solutions for the Trucks and Heavy Equipment
Reliability solutions that do the heavy lifting

Ordering Software from ReliaSoft
Choose the software license and configuration that's best fit for your organization

The Synthesis Platform
Integration to Empower the Reliability Organization

ALTA — Accelerated Life Testing Data Analysis Software
Accelerated life tests... quantifiable resultsTM

RENO — Simulation Software for Probabilistic Event and Risk Analysis
If you can flowchart it, you can simulate itTM

RBI — Risk Based Inspection (RBI) Software
Full-featured RCM with risk based inspection

ReliaSoft Offers Unparalleled Technical Support and Documentation
Connect with ReliaSoft's acclaimed technical support team via phone, email and live chat at locations throughout the world

The Synthesis Enterprise Portal
Open up the Synthesis Platform, and your work in the platform, to your whole organization with this web-based Portal

DOE++ — Software Tool for Experiment Design and Analysis (DOE)
DOE software designed with reliability in mindTM

Lambda Predict — Standards Based Reliability Prediction Software
MIL-217 * Bellcore/Telcordia * FIDES * NSWC Mechanical

MPC — MSG-3 Aircraft Maintenance Program Creation Software
An MSG-3 compliant maintenance program creator

Register and Activate Software from ReliaSoft
Start using ReliaSoft's helpful applications by registering and activating your software today!

The Synthesis API
Application Programming Interfaces (APIs) for the Synthesis Platform

RGA — Reliability Growth Analysis and Repairable System Analysis
Reliability growth and repairable system analysisTM

Xfmea — FMEA and FMECA Software Tool
Expert support for all types of FMEA and FMECATM

XFRACAS — Web-Based Failure Reporting Software (FRACAS System)
Highly configurable, web-based FRACAS

Download Free Software Demos and Free Service Releases
Get a free trial of the reliability engineering software trusted by thousands of companies, or install the latest update

Weibull++ — Life Data Analysis (Weibull Analysis) Software
The standard for reliability life data analysisTM

BlockSim — System Reliability and Maintainability Analysis Software
The ultimate system visualization and analysis toolTM

RCM++ — Reliability Centered Maintenance (RCM) Software
Putting the reliability back into reliability centered maintenanceTM

Orion eAPI — Enterprise System Focused on Asset Performance Management
Reliability Business IntelligenceTM

Reliability Education Course List
Peruse the variety of introductory and advanced courses offered for reliability practitioners of all experience levels

The New Master the Subject, Master the Tools!SM Course Lineup
Choose the reliability engineering education track that best fits your needs: Product Development or Asset Management

Worldwide Public Seminar Calendar
Find out which courses are being taught near you, and register today!

Why Attend ReliaSoft's Reliability Training Seminars?
Learn about ReliaSoft's commitment to reliability engineering education, as well as the benefits of attending

Seminar Registration
Register for a course today, and learn about seminar options and other details

Seminar Instructors
Meet the esteemed group of professionals that bring ReliaSoft's training seminars to life

Reliability Training Center (RTC)
Take a glimpse inside ReliaSoft's newly built, multimillion dollar state-of-the-art training facility

Frequently Asked Questions about ReliaSoft's Seminars
Get answers to the most common questions about the finer details of attending ReliaSoft's courses

Available Reliability Consulting Services
ReliaSoft's expert consulting team can accommodate requests of any size or complexity in a wide variety of areas

Customers that Benefited from ReliaSoft's Consulting Services
View some of the organizations that took advantage of ReliaSoft's on-site consulting and education services

ReliaSoft's Expert Reliability Consultants
Meet the esteemed group of professionals that organizations worldwide have trusted for their consulting needs

Frequently Asked Questions about ReliaSoft's Consulting Services
Get answers to the most common questions about the finer details of ReliaSoft's consulting services

About ReliaSoft Corporation
Learn more about ReliaSoft and its commitment to empowering the reliability professional

ReliaSoft Corporation Online Store
Purchase new software licenses, upgrades or resource books, and register for training seminars or ARS events

Companies that Rely on ReliaSoft's Reliability Software and Services
See some of the hundreds of companies that look to ReliaSoft for the tools necessary to power their reliability programs

Press Releases
Read about the latest exciting ReliaSoft product releases and announcements

Print-Ready Publications and Media Links
Browse for publications and videos relating to ReliaSoft's products and services

Career Opportunities at ReliaSoft Corporation
Want to work for the leading provider of reliability engineering software tools and services? Check for openings today!

How to Contact ReliaSoft Corporation
ReliaSoft offers unparalleled phone, e-mail and live chat support at locations throughout the world

ReliaSoft Customer Testimonials
"Amazing," "User Friendly," "Cutting Edge..." See what people are saying about ReliaSoft's software!

Weibull.com
The most complete website devoted entirely to the topic of reliability engineering, theory, data analysis and modeling

ReliaSoft.tv
A multimedia resource portal for both users of our software tools as well as all reliability engineering professionals

Synthesis Applications eDocuments & ePublications Library
Access useful support documents for ReliaSoft's Synthesis Applications, including user's guides and reference books

ReliaWiki
A resource portal and a wiki for professionals in reliability engineering and related fields

Certified Reliability Professional (CRP)
Distinguishing reliability engineers who have gained and successfully demonstrated unquestionable expertise in the field

International Applied Reliability Symposium (ARS)
Sharing applications, success stories and lessons learned in reliability and maintainability engineering

Reliability Discussion Forum
Connect, discuss and share with the reliability engineering community

Reliability Edge Newsletter
In-depth articles on reliability engineering theories and principles, as well as ReliaSoft updates

Reliability HotWire
Your source of information and tips on how to improve your reliability practices

Technical Support
Knowledge Base

Minimum database maintenance recommendations — SQL Server

Technical Support Document # RS40005

Last Edited: 05/04/2015

Product(s): All Synthesis Applications, Xfmea/RCM++ 5, XFRACAS

ReliaSoft's Synthesis-enabled applications, as well as earlier versions of Xfmea/RCM++ and XFRACAS, can be deployed with a SQL Server database for centralized data storage. With enterprise-wide, database-driven software applications such as these, it is important to have established procedures to assure that proper database maintenance and backup activities are being performed. These activities are the responsibility of the organization that implements the system (since ReliaSoft's support personnel do not have access to, or specific knowledge of, the IT infrastructure used to deploy the system). This document presents ReliaSoft's recommendations for the minimum database backup and maintenance activities that should be performed in SQL Server to protect the organization's data and keep the system running smoothly.

Click the appropriate link to jump to the instructions for your version of SQL Server. This document contains instructions for creating backups in:

Note: To view the instructions for creating backups in Oracle, see document number RS40006. To view the instructions for creating backups in SQL Server Express, please see RS40023.

SQL Server 2008 Backup Instructions

ReliaSoft makes these two minimum recommendations for maintaining SQL Server 2008 databases:

  1. Create a Database Maintenance Plan that creates a Full backup within SQL Server.
  2. Create a Database Maintenance Plan that creates a Transaction Log backup within SQL Server.

Create Full Backups Using a Database Maintenance Plan within SQL Server 2008

  • Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server 2008 > SQL Server Management Studio.
  • Expand Management and right-click Maintenance Plans. Choose Maintenance Plan Wizard.
  • Click Next to proceed past the start page and to the first step of the wizard. Type a descriptive name for your maintenance plan.
  • Make sure Single schedule for the entire plan or no schedule is selected (we will use the same schedule for all the tasks in this maintenance plan). Click the Change button to open the Job Schedule Properties window.
    • Choose Recurring for the Schedule type and select the Enabled check box. In the Frequency section, choose Occurs Daily and Recurs every 1 day. In the Daily frequency section, specify a time when the database is least utilized (e.g., 2:00 AM). Click OK.
  • Click Next to proceed to the Select Maintenance Tasks page.
  • In the Select Maintenance Tasks page, select Check Database Integrity, Shrink Database, Reorganize Index and Back Up Database (Full). Click Next.
  • On the Select Maintenance Task Order page, use the Move Up and Move Down buttons to arrange the tasks in the following order and then click Next.
    • Shrink Database
    • Reorganize Index
    • Check Database Integrity
    • Back Up Database (Full)
  • On the Define Shrink Database Task page click the down-arrow to the right of Databases and choose your database  from the list. Click OK to close the database selection window. Keep the default values for Shrink database when it grows beyond (i.e., 50 MB) and for Amount of free space to remain after shrink (i.e., 10%). Click Next.
  • On the Define Reorganize Index Task page, click the down-arrow to the right of Databases and choose your database from the list. Click OK to close the database selection window. Click Next.
  • On the Define Database Check Integrity Task page, click the down-arrow to the right of Databases and choose your database from the list. Click OK to close the database selection window. Click Next.
  • On the Define Back Up Database (Full) Task page, click the down-arrow to the right of Databases and choose your database from the list. Click OK to close the database selection window. Choose the media for the backup (i.e., Disk or Tape) and the location for the backup. Select Verify backup integrity. Click Next.
  • On the Select Report Options page, you can choose to have the maintenance plan generate reports. This is optional. Click Next.
  • The final page of the wizard summarizes the Maintenance Plan and the tasks that will be run. Click Finish.
  • The Maintenance Plan Wizard checks the settings and then adds the job to the SQL Server Agent Jobs and will run at the next scheduled time.

Return to top

Create Transaction Log Backups Using a Database Maintenance Plan within SQL Server 2008

Note: These instructions follow the same steps as above, but because we suggest running transaction log backups throughout the day (this may vary depending on your business needs), we suggest creating a separate Maintenance Plan, which has a more frequent schedule than once a day, for the transaction logs.

  • Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server 2008 > SQL Server Management Studio.
  • Expand Management and right-click Maintenance Plans. Choose Maintenance Plan Wizard.
  • Click Next to proceed past the start page and to the first step of the wizard. Type a descriptive name for your maintenance plan (e.g., MaintenancePlan-Transaction_Log).
  • Make sure Single schedule for the entire plan or no schedule is selected. Click the Change button to open the Job Schedule Properties window.
    • Choose Recurring for the Schedule type and select the Enabled check box. In the Frequency section, choose Occurs Daily and Recurs every 1 day. In the Daily frequency section, select Occurs every and choose 4 hours and Starting at 12:00 AM (assuming that you have followed the previous section's suggestion and set the full backup to 2:00 AM -- to ensure it will not be running at the same time as the full backup). Click OK.
  • Click Next to proceed to the Select Maintenance Tasks page.
  • On the Select Maintenance Tasks page, choose Back Up Database (Transaction Log). Click Next.
  • On the Select Maintenance Task Order page click Next.
  • On the Define Back Up Database (Transaction Log) Task page click the down-arrow to the right of Databases and choose your database from the list. Click OK to close the database selection window. Choose the media for the backup (i.e., Disk or Tape) and the location for the backup. Select Verify backup integrity. Click Next.
  • On the Select Report Options page, you can choose to have the maintenance plan generate reports. This is optional. Click Next.
  • The final page of the wizard summarizes the Maintenance Plan and the tasks that will be run. Click Finish.
  • The Maintenance Plan Wizard checks the settings and then adds the job to the SQL Server Agent Jobs and will run at the next scheduled time.

Return to top

SQL Server 7.0, 2000 and 2005 Backup Instructions

ReliaSoft makes two minimum recommendations for maintaining SQL Server 2005 and earlier databases:

  1. Set the Recovery Model to Full.
  2. Create a Database Maintenance Plan that creates a Full backup within SQL Server.

Note: The instructions in this section were prepared using SQL Server 2000. The options may vary for SQL Server 7.0 or 2005.

Set the Recovery Model to Full within SQL Server 2005 and Earlier

  • In the SQL Sever Enterprise Manager, right-click the database and select Properties.
  • Go to the Options page. Choose Full for the Recovery Model, as shown next. Click OK.

Set the Recovery Model to Full

Return to top

Create a Database Maintenance Plan within SQL Server 2005 and Earlier

  • Launch the SQL Server Enterprise Manager and use the hierarchy to navigate to your server. Under the server, expand the folder called Management.
  • Under Management, right-click Database Maintenance Plans and select New Maintenance Plan to start the Database Maintenance Plan Wizard.

Navigate in SQL Server

  • Click Next to proceed to the first page of the wizard. When prompted to select the databases for which to create the maintenance plan, ReliaSoft recommends choosing the application database, "master," "model" and "msdb," but only the application database is required.
  • Click Next to proceed to the next page of the wizard, Update Data Optimization Information, as shown below.
    • Select Reorganize data and index pages and Change free space per page percentage to 10.
    • Select Remove unused space from database files and keep the default value of 50 MB.
    • It is recommended to change the schedule to daily, to be done after hours. Also, if your backup is to tape, it is recommended to have this performed before that process. The time it takes to complete the maintenance depends on the size of your database and the hardware used to host the database. 

Update Data Optimization Information

  • Click Next to proceed to the next page of the wizard, Database Integrity Check, as shown below.
    • At minimum, select Check database integrity and Include indexes. Do not select Attempt to repair any minor problems since the given database must be in single-user mode to use this value.
    • You may accept the schedule default value of once a week or you may change the schedule.

Database Integrity Check

  • Click Next to proceed to the next page of the wizard, Specify the Database Backup Plan, as shown below.
    • Select the options to Back up the database as part of the maintenance plan and Verify the integrity of the backup when complete.
    • Set the location to Disk.
    • Schedule daily backups. If you have a system tape backup, then schedule the database backup to occur before the system tape backup.

Specify the Database Backup Plan

  • Click Next to proceed to the next page of the wizard, Specify Backup Disk Directory, as shown below.
    • You may use the default backup directory or specify your location.
    • If you specify the Create a subdirectory for each database option, the system backs up each database into separate folders.
    • Select the Remove files older than option, and specify how long to retain previous backup files. Usually, removing files older than two weeks will be adequate. Of course, this depends on the database size, the amount of free space on the system hosting the database and your organization's preferences.

Specify Backup Disk Directory

  • Click Next to proceed to the next page of the wizard, Specify the Transaction Log Backup Plan, as shown below.
    • Select the options to Back up the transaction log as part of the maintenance plan and Verify the integrity of the backup when complete.
    • It is recommended to backup the transaction logs every 2 to 4 hours during normal business hours. Each environment is different, so you should set these options based on the acceptable amount of data loss in case of a failure.

  • Click Next to proceed to the next page of the wizard, Specify Transaction Log Backup Disk Directory, as shown below.
    • It is recommended that you choose the same options as you did for the database backup location.

Specify Transaction Log Backup Disk Directory

  • Click Next to proceed to the next page of the wizard, Reports to Generate, as shown below.
    • If you choose, you can have the maintenance plan generate reports. This is optional.

Reports to Generate

  • Click Next to proceed to the next page of the wizard, Maintenance Plan History, as shown below.
    • The default options are usually adequate. You can view the history by right-clicking the Maintenance Plan under Database Maintenance Plans under Management and selecting Maintenance Plan History.

Maintenance Plan History

  • Click Next to proceed to the final page of the wizard, as shown below.
    • Name your plan and click Finish.

Execution of the Maintenance Plan requires that the SQL Server Agent be active. The system displays an error message if the SQL Server Agent is not active. If necessary, activate the SQL Server Agent the same way you activated SQL Server.

  • To start this service, click the SQL Service Manager located in your system tray by the clock.
    • Select SQL Server Agent and start it. You will probably want to select the Auto-start service when OS starts check box also.
    • After this, your maintenance plan should run as scheduled. 

SQL Server Service Manager

Return to top

ReliaSoft.com Footer

Copyright © 1992 - 2015 ReliaSoft Corporation. All Rights Reserved
Privacy Statement | Terms of Use | Site Map | Contact | About Us    ReliaSoft on Facebook  ReliaSoft on Twitter  ReliaSoft on LinkedIn  ReliaSoft on Google+