Backup strategies

Information technology (IT) professionals fall into one of two categories: those who have already experienced a significant data loss, and those who will. The survivors will be clearly distinguished from the casualties by the planning and implementation of their backup and restore strategies.
Investment in a backup and restore strategy and implementation plan is not inexpensive, nor is it optional. If a business considers data important, then it must focus on data protection.

Backup strategies

Questions you should be asking when defining your backup strategy:
What type of database backup will be used?
Who will perform the database backup?
How frequently will a backup be performed?
Who will verify that the backups have completed successfully and what steps will be taken to verify the backup?
What hardware and software is necessary to ensure a successful backup?
Which directories and files need to be backed up to ensure a complete system backup?
Is your backup plan documented as part of your written procedures and is that plan tested on a regular basis?
Where will the database be backed up to, tape devise or fixed disk for example?
Have you established a backup device in SQL Server Enterprise Manager?

What do you need to backup the Fourth Shift system​

The backup process is an essential part of a data protection plan for preventing data loss. The Fourth Shift SQL architecture contains system data in a SQL database called FSDB(System Id) and Fourth Shift system folders:

Access databases for additional modules
BEXE data extracts in Dbase,Access and Excel formats
Internal system definitional files

The Fourth Shift system folder structure and the SQL database are backed up:

FSDB database and transaction log

The following folders:

<System root drive>
Mfgsys
Mfgsys\I
XPort
Apbm
Avsm
Cstm
Edim
FS_Apps\D
Mfgsys\Cssm
Mfgsys\Engm
Mfgsys\Saam
Mfgsys\Sfrm
Opfm
3rd party application folders
User specific folders

All these folders could contain active data depending on Fourth Shift modules purchased

Backup frequency

There are 3 types of SQL backup processes:

Full Database backup: a complete database backup including data and transactions
Transaction Log backup: There are 2 types of file behind a SQL database a data file(s) (MDF file extension) and a transaction log file(s)( LDF file extension). The transactional log backup contains all transactions since last complete or transactional backups.
Differential backup: Is not typically used and is nor covered in this article

Although businesses do vary in frequency and types of backup these are common guidelines

Typically a complete backup of FSDM database to file and folders are backed up before overnight batch execution task and after called Pre and Post backups. These backups are processed in the overnight process. This task typically happens at the end of the working day typically 07:00PM or 10:00PM and can take a few hours to complete
It is advisable to store backup files on a separate server to your Fourth Shift server. This enables data recovery if Fourth Shift server or hard disk failure occurs
A full tape backup of all server files completed which will include the pre and post overnight process backup files which typically happens in the early hours
Transactional log backups scheduled to execute every hour outputting a backup file which is moved to another server during the working day

Performing a SQL database backup​

Define back up devices
Open SQL Server Enterprise manager and navigate to management category
Select backup icon right click and click new backup devices

Provide name for the backup device
Select either tape drive name or file name

Performing and scheduling a backup using the SQL Enterprise manager:
Open SQL Server Enterprise manager and right click on the Fourth Shift SQL database for example: FSDDBMR select all tasks and click on backup database
Provide a name and description for the database backup

Add an appropriate description
Leave the Database – Complete checkbox set
Select the backup device or add a new device and click OK
NOTE: You may need to remove a default device selection by pressing the remove button and click OK

Automatic backup schedule

Click the schedule check box and click build button

Make sure the Enabled checkbox is set
Initially decide on occurrence and occurrence settings
Daily frequency enables once or frequent backup options. The occurs each option is used to define hourly transaction log backups

Backup Options

Click options tab to view additional backup options
It's always a good idea to verify your back up on competition
Click OK to save database back up schedule job
To execute Job
Open enterprise manager and navigate to SQL job agent to observe SQL job created for backup process right click and start job to execute immediately

Historical logs can be checked to ensure process is functioning correctly

NOTE: The process of backing up in overnight script file will be covered in overnight process

Fourth Shift Maintenance Plan

The Database Maintenance Plan can be used to improve system performance, check for database corruption and perform full and transactional log backups. Two database maintenance plans are recommended for a Fourth Shift environment:

Fourth Shift database maintenance plan
System database maintenance plan

Defining a Fourth Shift database maintenance plan

Open SQL Server Enterprise Management and navigate to management category right click on database maintenance plan and click on New Maintenance Plan

The Database Maintenance Plan Wizard will walk you through the process of defining the plan
Select the Fourth Shift database

Choose desired options in update data optimisation. We recommend reorganize data and index pages and remove unused space once a week out of working hours

Bypass the Database Integrity check due to potential issues with the repair minor problems option
The full backup occurs in the overnight process so we would recommend bypassing Database backup plan
The transaction log backup plan is used to configure our hourly transaction log backups. Configure the schedule to run hourly throughout working hours:

The location for backup log files can be configured and cleared down automatically according to a time interval

The reports to generation can be useful for producing logs to monitor your database maintenance plan. Set the write file folder to \Logs for example: M:\Logs (make sure the folder exists)

The process will automatically name the files with date references and also delete according to the delete files older setting
Bypass the Maintenance plan history because log files will be used to monitor
Use an appropriate plan name for your Fourth Shift database maintenance plan

Checking your database maintenance plan

A few SQL agent jobs are produced automatically to carry out the actions of your Fourth Shift Database maintenance Plan

Historical logs can be checked to ensure process is functioning correctly

Fourth Shift Backup

Get help with your Fourth Shift Backup requirements