54 Chapter 5 Services and Database Administration
Backing up SQL Server data files
You back up SQL Server data files to ensure you can replace corrupted or lost data as a result of media
problems, user errors, hardware failures, and natural disasters. Please review Microsoft procedure for
creating a recovery model that controls the backup and restores operations for a database. See http://
www.exforsys.com/tutorials/sql-server-2005/sql-server-backing.html

Understanding SQL Server recovery models

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:
1. Simple
2. Full
3. 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
http://msdn.microsoft.com/en-us/library/ms175987(SQL.90).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:
1. The importance of the data being stored in the SQL Server database.
2. 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.