The following section details SQL Server recovery models and how they relate to backups and disk space usage. There are three recovery models available for use with SQL Server:
- Simple
- Full
- Bulk logged
For detailed information on backing up SQL Server through SQL Server Management Studio, see http://msdn.microsoft.com/en-us/library/ms187510.aspx.
For a complete overview of SQL Server recovery models, see https://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx
By default, SQL Server Express uses the Simple recovery model. Retail versions of SQL Server use the Full recovery model. There are two things you must consider when deciding which recovery model to use:
- The importance of the data being stored in the SQL Server database.
- The amount of disk space used by the transaction log file.
The only way to recover a SQL Server database is to restore it from a SQL Server backup. The type of recovery model you use impacts the ability to restore the database.
The Simple recovery model is recommended for customers who do not have an IT department upon which to rely for regular backups and who believe the summarization process is sufficient to re-generate historical data. There are several things that should be considered when using the Simple recovery model:
- The log file does not continually grow and is truncated each time a successful transaction is completed.
- When the log file is 80% full, the log will automatically clear out old transactions and rewrite the log file with the newer transactions.
- Point-in-time recovery is not supported with this model and the database can be restored only from the last full or differential backup.
- This model is ideal when the data in the database is not considered mission critical, is being backed up periodically, or can be restored from another means, such as summarization.
The Full recovery model is recommended for customers who have an IT department that can manage scheduled backups. There are several things that should be considered when using the Full recovery model:
- The log file will grow until a transaction log backup is successfully completed against the SQL Server database. Transaction logs can be applied to full database backups to ensure point-in-time recovery, up to the time when the last transaction log backup was taken.
- Point-in-time recovery is supported with this model
- This model requires a regular backup schedule and sufficient disk space to house the log file as it grows between backups.
Frequency and scope of configuration changes may be a suitable indicator in determining which recovery model to use. If configuration changes are infrequent and point-in-time recovery is not critical, the Simple recovery model may be sufficient. However, when using the Simple recovery model, we highly recommend that SQL Server backups are performed after any significant configuration changes have been made to the database.
Regardless of the recovery model used, it is strongly recommended that the data files collected by the MiContact Center Business applications are backed up on a regular basis, as these files are used in the summarization process to re-generate historical data.