Data Backup Overview#

The scope of a backup of data can be a whole database, a partial database, or a set of files or filegroups. For each of these, SQL Server^TM supports full and differential backups. A full backup of the whole database represent the whole database at the time the backup finished. Differential backup contains only the data extents that were modified since the most recent database backup of each file.

Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup. After the first data backup, under the full recovery model or bulk-logged recovery model, regular transaction log backups (or log backups) are required. Each log backup covers the part of the transaction log that was active when the backup was created, and the log backup includes all log records that were not backed up in a previous log backup.

Database Backups#

Database backups are easy to use and are recommended whenever database size allows. SQL Server supports the following types of database backups.

Table: Database full backup types

Partial Backups#

Partial and differential partial backups are designed to provide more flexibility for backing up databases that contain some read-only filegroups under the simple recovery model. However, these backups are supported by all recovery models.

SQL Server^TM supports the following types of file backups.

Table: Database partial backup types

File Backups#

The files in a database can be backed up and restored individually. Using file backups can increase the speed of recovery by letting you restore only damaged files, without restoring the rest of the database.

For example, if a database consists of several files that are located on different disks and one disk fails, only the file on the failed disk has to be restored. However, planning and restoring file backups can be complex; therefore, file backups should be used only where they clearly add value to your restore plan.

SQL Server^TM supports the following types of file backups.

Backup type	Description
File backup	A full backup of all the data in one or more files, or filegroups. Under the simple recovery model, file backups are basically restricted to read-only secondary filegroups. You can create a file backup of a read/write filegroup, but before you can restore the read/write file backup, you must set the filegroup to read-only and take a differential read-only file backup.
Differential file backups	A backup of one or more files that contain data extents that were changed since the most recent full backup of each file. Under the simple recovery model, this assumes that the data has been changed to read-only since the full backup.

Table: File backup types

Please refer to the article Backup Overview (SQL Server) ⧉ for detailed information.

Data Recovery Overview#

Recovery Model Overview#

Recovery models are designed to control transaction log maintenance. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model.

The following table summarizes these recovery models.

Table: Recovery models

Please refer to the Recovery Model Overview ⧉ for more information.

Restore General Assumptions#

The restore procedure described in this document assumes the following:

• The Critical Manufacturing Online, ODS and DWH databases are installed with the Full Recovery Model. The end-user can change this configuration to Simple or Bulk-logged, but then the databases will only be recoverable to the most recent backup or to the end of a transaction log backup when the log backup contains bulk changes, respectively. In order to prevent data loss in the broadest range of failure scenarios, the recommended recovery model for Critical Manufacturing is the Full Recovery Model. Please refer to Backup Under the Full Recovery Model ⧉ for more information.
• A Backup/Restore policy is in place for all the Critical Manufacturing databases (Online, ODS and DWH). All backup jobs (Full, Diff and Log) are running without errors. The recommended backup intervals are:
  • Full backups: once a week
  • Diff backups: twice a day
  • Log backups: every 5 minutes
• Full, Diff and Log Backup files are stored in a safe location and accessible

Automated Restore (During Installation)#

1. Ensure correct naming: Downloaded full backup packages already follow the required naming convention. Per example for MES v11.2.0:

   • MES main database: Cmf.FullBackup.Online.11.2.0.zip
   • DWH database: Cmf.FullBackup.DWH.11.2.0.zip
   • ODS database: Cmf.FullBackup.ODS.11.2.0.zip

   Full backups with the system name

   To support restoring different environments from the same installation packages directory, logic was added to prioritize restoring full backups from files that include both the system name and the version in their name.
   For example, assuming system name MES and version 11.2.0, the application will first search for the file:
   MES.Cmf.FullBackup.Online.11.2.0.zip
   If not found, it will fall back to the previous default naming:
   Cmf.FullBackup.Online.11.2.0.zip.

2. Place the full backup packages in the MES customization installation packages directory without renaming them.

3. Run the MES installation process. The installer will automatically detect and restore from the full backup packages.

Tail-log Backups#

In case of a database crash and in order to restore it up to the last transaction immediately before the crash, it’s necessary to do a backup of the current database log. This is called a Tail-Log backup and it will capture all the log records that have not yet been backed up. This is the first step of the restore process.

If the database is available and online, a tail-log backup is taken by using the command:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

If the database is offline and does not start, it’s also possible to try the following command:

BACKUP LOG database_name TO <backup_device> WITH CONTINUE_AFTER_ERROR

If the database is damaged, for example, if the database does not start, a tail-log backup succeeds only if the log files are undamaged, the database is in a state that supports tail-log backups, and the database does not contain any bulk-logged changes. If the log files are damaged and a tail-log backup cannot be created, you must restore the database without using a tail-log backup. Any transactions committed after the latest log backup are lost.

The tail-log backup prevents work loss and keeps the log chain intact. When you are recovering a database to the point of a failure, the tail-log backup is the last backup of interest in the recovery plan. If you cannot back up the tail of the log, you can recover a database only to the end of the last backup that was created before the failure.

Please refer to Tail-Log Backups (SQL Server) ⧉ for more information.

Recovery Scenarios#

Depending on the specific database that crashed and also type of crash, being a high availability application, Critical Manufacturing restore operations will require different approaches. This section addresses four scenarios:

• Full Database Cluster Crash (Online + ODS)
• Online Database Crash
• ODS Database Crash
• Recovering ODS Database Transactions

The Restore operation is highly dependent on what caused the system crash in the first place. This section provides a summary of the steps necessary to bring Critical Manufacturing back online taking into consideration the most probable scenarios. All of the operations here described are based on Microsoft documentation and were tested under forced crash conditions.

The aim of the Critical Manufacturing backup/restore solution is to minimize data loss in case of a system/database crash. As described, there is always the possibility to lose transaction data (if Online tail-log backup not possible). The idea is always to minimize this risk and, in case of an occurrence, minimize the amount of lost data. Therefore we recommend the transaction log backup interval to be 5 minutes. In this case the data loss will always be 5 minutes in the worst case.

Multiple occurrences might also influence the full system recovery (e.g. unavailable or corrupt backup files) but these situations are common to any database dependent application. There are multiple solutions that will guarantee the high availability of all the backup files. It all depends on the data loss risk assessment done by the end-user.

For additional information on Database Backups and Restores, please refer to the following links

• Restore and Recovery Overview (SQL Server) ⧉
• Backup Under the Full Recovery Model ⧉
• Tail-Log Backups (SQL Server) ⧉
• Piecemeal Restores (SQL Server) ⧉
• SQL Server Backup, Integrity Check, and Index and Statistics Maintenance ⧉

Full Database Cluster Crash (Online + ODS)#

This is the worst case scenario. After the hardware is recovered, the system administrator must first concentrate on recovering the Online database. This will allow bringing the Critical Manufacturing Host, Critical Manufacturing GUI and other dependent systems online and will enable the users to start working on the system.

Online Database Crash#

By design, the Online database will only contain the current object status and their recent activity history, thus enabling fast restore operations. Depending on the state of the database after the crash, it might not be possible to issue a tail-log backup, therefore losing all the transactions made since the last transaction log backups. This is the reason we recommend issuing transaction log backups every 5 minutes (see Restore General Assumptions) in order to minimize the impact of such a situation.

Once the Online database is restored, Critical Manufacturing can start being used. The system administrator can now focus on recovering the ODS database (if this is the case).

Please note that the replication jobs run from the ODS database. Once the ODS and replication database is restored, the replication job will resume from the latest LSN (Log Sequence Number) applied in the ODS database.

ODS Database Crash#

Depending on the end-user requirements, ODS database can contain several years of historical data. As long as the SQL Server Database edition enables it, Critical Manufacturing is prepared to use SQL Server Partitioning technology in order to cope with such high volume of data. Please refer to SQL Server Documentation Release Notes ⧉ for comparison of all the available SQL Server editions and its functionalities such as table and index partitioning under Scalability and Performance.

In the case that SQL Server partitioning is available, we recommend using Piecemeal Restores, thus enabling the partial restore of filegroups, starting with the primary filegroup, followed by the current partition filegroup and finally with the rest of the missing partition files. This operation will bring ODS Database partitions online in a timely manner, and not depending on a single and time consuming restore operation. Please refer to Piecemeal Restores (SQL Server) ⧉ for more information.

If the SQL Server database does not allow a table and index partitioning scenario, the restore operation is made in a similar manner as the Online Restore operation.

If it is not be possible to issue a tail-log backup, the last ODS Database transactions will be lost. but, in this case, as we will see below, it will be possible to recover these transactions from the Online Database.

Recovering ODS Database Transactions#

ODS database relies on a replication mechanism in order to continually receive the data manipulation operations done in the Online database. In other words, all the modifications done in Online database will be applied, exactly in the same order, in the ODS database. As explained earlier, for performance issues, Online database must only contain the current WIP and recent activity history. This is achieved by Critical Manufacturing specific maintenance jobs that will purge old data from Online and will keep this same data in ODS (delete operations are replicated and handled in ODS for some tables and ignored for others). This purge jobs will only purge a specific record from Online after it was possible to cross-check that this exact same record is found in ODS, i.e. the transaction replicated and applied in ODS. This mechanism also guarantees that the deleted records follow a chronological order (older first) and will only delete records found in a specific time frame. This will guarantee that even if the ODS tail-log is not available we will still have the exact same records in Online.

The replication mechanism architecture guarantees the convergence of the latest Online records not yet found in ODS, therefore it will resume the data movement from the last successful replication operation existing in ODS.

Backup and System Maintenance Recommendations#

By default, Critical Manufacturing setup installs basic database maintenance tasks. These procedures implement the recommended backup strategy and maintenance tasks. These are:

1. Database backups
2. Index maintenance
3. Statistics refresh
4. Database integrity checks

All the database scripts are based on the SQL Server Maintenance Solution published and maintained by Ola Hallengren (available here ⧉). Each task consists of a specific procedure, its configuration and a schedule for execution. These tasks are installed as a job directly in the database under SQL Server Agent.

1. DatabaseBackup - SYSTEM_DATABASES - FULL This job is responsible for the full backup of all the system databases (master, model and msdb). Runs weekly on Sunday at 04h00.

2. DatabaseBackup - USER_DATABASES - FULL This job is responsible for the full backup of all the user databases in the current SQL Server instance. Runs weekly on Sunday at 05h00.

3. DatabaseBackup - USER_DATABASES - DIFF This job is responsible for the differential backups of all the user databases in the current SQL Server instance. Runs daily at 00h10 and 12h10.

4. DatabaseBackup - USER_DATABASES - LOG This job is responsible for the log backups of all the user databases in the current SQL Server instance. Runs daily every 5 minutes.

5. DatabaseIntegrityCheck - SYSTEM_DATABASES Runs database integrity checks (CHECKDB) against the system databases (master, model and msdb). Runs weekly on Saturday at 01h00.

6. DatabaseIntegrityCheck - USER_DATABASES Runs database integrity checks (CHECKDB) against the user databases in the current SQL Server instance. Runs weekly on Saturday at 02h00.

7. IndexOptimize - USER_DATABASES Runs index maintenance activities (reorganize or rebuild depending on fragmentation thresholds). It also updates tables statistics. Runs weekly on Saturday at 03h00.

8. sp_delete_backuphistory Deletes backup history older than 30 days. This is important to maintain the msdb database and to avoid backup bottlenecks. Runs weekly on Saturday at 00h30.

9. sp_purge_jobhistory Deletes SQL Server job history older than 30 days. Runs weekly on Saturday at 00h30.

10. Output File Cleanup Deletes the maintenance job log files after 30 days. Runs weekly on Saturday at 00h30.

11. CommandLog Cleanup Deletes the command log table entries older than 30 days. By default each command ran by the maintenance jobs is logged to [master].[dbo].[CommandLog] table. Runs weekly on Saturday at 00h30.

By default only one full database backup copy will be maintained. This will enable the recovery to the latest point in time (full+diff+logs). If you have a different recovery objective you can change the @CleanupTime parameter in the individual backup job command. If no time is specified, then no backup files are deleted. Note that DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted.

Although these scrips are commonly accepted as a complete solution for maintaining a healthy SQL Server Database, there are others that do the same job. If you use other solutions, please disable the backup jobs.

We strongly recommend that you maintain the same backup strategy described here as also the recurrence of each individual job.

Also note that the maintenance scripts generate extra stress on the disk and network systems. In order to balance the disk and network usage with other running systems, you may adjust the time at witch each job runs. Under any circumstance change the recurrence of each individual jobs.

Adapting Jobs to Target Environment#

The SQL commands generated for these jobs were designed out-of-the-box specifically for SQL Server Enterprise. If the SQL Server where they are deployed is using a different SQL Server Edition, these Maintenance Job commands should be reviewed and adapted according to the SQL Server Edition limitation. See https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16&preserve-view=true#rdbms-high-availability ⧉ for more information.

Relevant details about job behavior and its possible settings should be consulted on the respective documentation page on https://ola.hallengren.com/ ⧉.