arrow_back_ios

Main Menu

See All Software See All Instruments See All Transducers See All Vibration Testing Equipment See All Electroacoustics See All Acoustic End-of-Line Test Systems See All Academy See All Resource Center See All Applications See All Industries See All Services See All Support See All Our Business See All Our History See All Global Presence
arrow_back_ios

Main Menu

See All nCode - Durability and Fatigue Analysis See All ReliaSoft - Reliability Analysis and Management See All Test Data Management See All DAQ Software See All Drivers & API See All Utility See All Vibration Control See All High Precision and Calibration Systems See All DAQ Systems See All S&V Hand-held Devices See All Industrial Electronics See All Power Analyzer See All S&V Signal Conditioner See All Acoustic See All Current and Voltage Sensors See All Displacement See All Force sensors See All Load Cells See All Pressure See All Strain Gauges See All Temperature Sensors See All Torque Sensors See All Vibration See All Accessories for Vibration Testing Equipment See All Vibration Controllers See All Measurement Exciters See All Modal Exciters See All Power Amplifiers See All LDS Shaker Systems See All Test Solutions See All Actuators See All Combustion Engines See All Durability See All eDrive See All Production Testing Sensors See All Transmission & Gearboxes See All Turbo Charger See All Training Courses See All Acoustics See All Asset & Process Monitoring See All Custom Sensors See All Data Acquisition & Analysis See All Durability & Fatigue See All Electric Power Testing See All NVH See All Reliability See All Smart Sensors See All Vibration See All Weighing See All Automotive & Ground Transportation See All Calibration See All Installation, Maintenance & Repair See All Support Brüel & Kjær See All Release Notes See All Compliance See All BKSV Worldwide Contacts
arrow_back_ios

Main Menu

See All API See All Microphone Cartridges See All Microphone Sets See All Microphone Pre-amplifiers See All Sound Sources See All Acoustic Calibrators See All Special Microphones See All Accessories for acoustic transducers See All Experimental testing See All Transducer Manufacturing (OEM) See All Piezoelectric Charge Accelerometers See All Piezoelectric CCLD (IEPE) accelerometers See All Electroacoustics See All Noise Source Identification See All Environmental Noise See All Sound Power and Sound Pressure See All Noise Certification See All Industrial Process Control See All Structural Health Monitoring See All Electrical Devices Testing See All Electrical Systems Testing See All Grid Testing See All High-Voltage Testing See All Vibration Testing with Electrodynamic Shakers See All Structural Dynamics See All Machine Analysis and Diagnostics See All Dynamic Weighing See All Vehicle Electrification See All Calibration Services for Transducers See All Calibration Services for Handheld Instruments See All Calibration Services for Instruments & DAQ See All On-Site Calibration See All Resources See All Software License Management

Minimum Database Maintenance Recommendations with SQL Server

 

ReliaSoft desktop applications 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 our support personnel do not have access to, or specific knowledge of, the IT infrastructure used to deploy the system). This document presents our 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 http://www.reliasoft.com//content/hbkworld/global/en/migration/reliasoft/en/using-oracle-enterprise-databases.html. To view the instructions for creating backups in SQL Server Express, please see http://www.reliasoft.com//content/hbkworld/global/en/migration/reliasoft/en/sql-server-express-backup-instructions.html.

 

SQL Server 2008 Backup Instructions

We make 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.
  3.  

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 IntegrityShrink DatabaseReorganize 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 and 2000 Backup Instructions

We make two minimum recommendations for maintaining SQL Server 2000 and earlier databases:

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

Set the Recovery Model to Full within SQL Server 2000 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 2000 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, we recommend 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

 
Information is provided "as is" without warranty of any kind.