Database Files, Recovery Models and Backups Part 1

In your Microsoft SQL Server database, there are a minimum of two database files: an .MDF and .LDF file.  The .MDF file contains the data for your database, while the .LDF file is the transaction log file for your database.  Each of these files will grow as your data grows, but depending on your recovery model, your .LDF file may fill up at a slightly quicker pace.

There are three recovery models in Microsoft SQL Server 2012.  They are as follows:

• Simple
• The .LDF space is automatically reclaimed when new transactions have been entered.
• No transaction log backups available.  Since no transaction log backups are created, if your database goes down for any reason, any transaction changes made on the database since last full backup would be lost and need to be re-created.
• Bulk Logged
• Transaction log backups are created, which allows for data recovery from last backup, but not point in time.
• Fully logs regular transactions, but minimally logs bulk transactions.
• Full
• The .LDF file continues to grow as you have more transactions in the database.
• Log backups are available.  Data can be restored from the last log backup, which would first need to be accompanied by a restoration from a full database backup.

For more info on the different recovery models, visit here: Recovery Models and for more info on bulk logging, visit here: Bulk Logs

To know which recovery model you should select, first review the type of data contained within the database.  For example, if your company manages online transactions, you will more than likely want to make sure your data is backed up regularly (Full Recovery Model).  If, on the other hand, your database pulls data from other servers and runs reports on the data, you could get by with the Simple Recovery Model (granted that you have a backup copy of the database schema).

A company I used to work for had our main database, which housed our real time data, and a second server that used SSIS packages to create tables, pull the data and then summarize it.  We could get by with the Simple Recovery Model on the report server, but we needed the Full Recovery Model on the real-time servers.

Tomorrow we will cover what to do about growing database logs and backups.