There is a wealth of information about this, but I have to believe that some people just don’t get it.

Let’s start with the easiest, Simple Recovery mode.  You only have one choice for a restorable backup, and that’s a full backup.  When you back up the database, the log (committed transactions) are truncated, allowing the log file (.ldf) to maintain its smallest available size.  There is no transaction log backup for point-in-time restore, so you restore from latest backup.  SQL Express only runs in Simple Recovery mode.

In Full Recovery, you back up the complete database (full backup), but you MUST also back up the transaction log.  If you do not run periodic Transaction Log backups, the log file (.ldf) will continue to grow until it either reaches a preset limit or (more often) fills the drive.  The Transaction Log backup is what clears and truncates the log.  For restoring, you would need the last full backup plus any transaction logs up to the point of desired recovery.

Companies who use a software-specific backup agent for SQL backups are most commonly running full backups to their tape, the cloud, or other media.  If your database is set for Full recovery, you must back up the Transaction Log as well either to the same destination using the agent, or set up a SQL job to back up the log separately.  If this is the case, you would restore from full tape backup then any subsequent transaction log backups to the point of desired recovery.

I’ve seen too many cases where a company will choose a full recovery model to minimize data re-entry in the event of drive or human error, but then only runs full backups to external media.  The result?  A huge log file, or a drive that is out of space, stopping any additional data input until the condition is corrected.

Instructions for fixing full drives are available from Microsoft and many other sites, so I will not cover them here.

If you’re not certain what your database settings are for each of your databases, run this query in SQL 2005 or later:

select name, DATABASEPROPERTYEX(name, ‘Recovery’) Model from sys.databases

Bottom line, use the right backup for your recovery model and method.

Advertisements