Recently I found some SQL Server database issues at customer sites that involved some common misconceptions of database files and database backups. This blog post will describe how to avoid these issues and provide background on database backups and recovery models. The focus of this post is on control system application databases and data logging databases.
At one site a database was used to log data using Rockwell FactoryTalk Transaction Manager into many tables. The database size will grow substantially as more data is logged, which is expected. However, the database transaction log had grown to over 250 gigabytes for a 10 GB database, impacting the ability to take a virtual machine snapshot. The transaction log file growth will occur when the recovery model is set to Full and log backups are not taken. Details on how to avoid this problem are described below.
In another case, a server hardware failure occurred, but there was no recent database backup. Automated database backups were not scheduled. Eventually a new server was built, and the database files were recovered from the hard drive, and the files were attached to the SQL Server instance while offline to get the database back up and running. This worked, but automated database backups are the preferred solution.
SQL Server Database Files
SQL Server stores databases in two types of files:
- The data and table indexes are stored in a data file. There is one Primary Data File per database, and for some databases there will be one or more Secondary Data Files. For most data logging applications there will only be a Primary Data File.
- The Transaction Log contains a log of each database transaction and changes to the database structure and data. The behavior of the Transaction Log is controlled by the selection of the Recovery Model, which is described later in this blog post.
Database Backups
A Database Backup is not simply a copy of the database files. A database backup overwrites or appends a backup to a backup file while the database is running. It can be performed manually or be scheduled to occur automatically at predefined intervals or at specific times during the day.
A Full Backup will back up the entire database. A Differential Backup will back up the changes to the database since the last Full Backup was taken. A Differential Backup will run faster than a Full Backup and take less space; therefore, Differential Backups are scheduled between Full Backups, especially for larger databases. For example, a Full Backup can be taken once per week, and a Differential can be taken daily.
Transaction Log Backups
A Transaction Log backup loads the transaction log to a transaction log backup file and truncates the log file, which removes transactions that have been completed and committed. A transaction log backup can only occur when the Full or Bulk Logged Recovery Models are used and is required when using these recovery models; otherwise the transaction log file will grow indefinitely. The log backups should be taken more frequently than the database full and differential backups. Log backups are incremental backups. To restore the database to the last log backup time, a complete chain of log backups is needed from the last full or differential backup. If the log file is still available, a backup of the log can be taken to allow a restore to a specific point in time after the last log backup.
Recovery Models and Database Restore
A database can be restored to a different server or the same server. A database restore can be used to copy or move a database; recover from a database corruption or accidental deletion/modification of data; and to recover from a server software or hardware failure. How a database is restored is determined by the recovery model selected for the database.
SQL Server defines three recovery models that are used to adjust the behavior of the transaction log. A database recovery includes the complete sequence of database restores, to bring the database back operational with minimal data loss.
The Recovery Models are:
- Full Recovery Model
- Bulk Logged Recovery Model (a variation of the Full)
- Simple Recovery Model
When using the Full Recovery Model, the database can be restored to a specific point in time, if the transaction log is available. The sequence to restore when using the Full Recovery Model or Bulk Logged Recovery Model is to:
- Take a transaction log backup of the “tail of the log”.
- Restore the Full Backup file(s).
- Restore the last Differential Backup file(s).
- Restore all the Transaction Log Backups, in sequence.
- Restore the “tail of the log” Transaction Log backup (if taken).
Full Recovery Model: Restore Options
Full Recovery Model: Restore can be set to a specific time.
The Bulk Logged Recovery Model is a variation of the Full Recovery Model, which includes minimal logging for bulk transactions, database modifications, and index rebuilding. The database can be set temporarily to this model during these activities. This would be handled by your IT or DBA personnel.
When using the Simple Recovery Model, the database can be restored to the last Full or Differential Backup only. Since the Transaction Log is truncated after the completion and commit of transactions, there are no transactions in the log that can be recovered. The sequence to restore when using the Simple Recovery Model is to:
- Restore the Full Backup file.
- Restore the last Differential Backup file, if any.
Choosing a Recovery Model
The recovery model can be changed while the database is in operation, but typically either the Full or Simple recovery model is chosen. Your choice should be based on:
- Business requirements
- Regulatory requirements
- Availability of support from IT, DBA, or vendor
- Ability to document and validate the backup and restore process
Using the Full Recovery Model is the recommended practice for most production databases. Yet using the Full Recovery model can be problematic without IT, DBA, or vendor support. Maintaining the backup files, backup and restore procedures, and testing the procedures is time consuming. Although the Full Recovery Model will give you the least amount of data loss in the event of a failure, it also is more difficult to implement, requires thorough testing and training, and may require the ability to test on a separate computer. It is also essential that transaction log backups occur.
For non-critical systems or if you are not prepared to manage a recovery using the Full Recovery Model, then it is preferable to use the Simple Recovery Model. Transaction Log backups are not needed when using this model. In many situations, a daily Full Database backup will suffice. In some cases, data logging can be cached using store and forward on the source system, so minimal data will be lost while the SQL Server system is down. For many data logging applications, using the Simple Recovery Model is a reasonable choice and much better than no backups or an ever-growing transaction log.
Recovery Model Selection Decision Tree
Conclusion
Select a database recovery model that best meets your business requirements for your controls system and data logging databases. Document and test the backup and recovery procedure and validate the backup and restore process on a test server. Periodically verify that the correct recovery model is selected, that the transaction log is not continuously growing, and your automated backups are occurring as expected. Hopefully this blog post clears up some common misconceptions about database files and database backups.
About the Author
Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.
Read My Hallam Story
About Hallam-ICS
Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.
