Full Recovery Model Without Log Backups

When a database is in Simple Recovery Model, SQL Server does circular logging: it goes back to the beginning of the transaction log and reuses space when it can.  Portions of the log are freed up when they’re not covering open transactions.

Full and Bulk Logged Recovery Models Require Log Backups to Re-use Portions of the Log

On the other hand, when a database is in Full Recovery Model or Bulk Logged Recovery Model, SQL Server doesn’t free up the log file when your transactions finish.  It will continue to grow the transaction log, thinking that you’ll want to back up all of these logs at some point.  (You can learn more about Recovery Models from Books Online.)

We check to see if there’s any databases in Full Recovery or Bulk Logged Recovery that haven’t had a transaction log backup in the last 7 days.  Granted, you should be doing ’em much more frequently than that, but we gotta start somewhere!

To Fix the Problem (in a nutshell)

We have a couple of choices here:

1. You can put the databases in SIMPLE recovery model. This does not require transaction log backups, but if there’s corruption, an accidental delete, or any problem, you lose all data since the last full or differential backup. You do not have the ability to restore to a point in time.
2. You can start taking transaction log backups. If this data is critical to the business, this is probably the right answer! Get the details on how to implement this below.

Talk to the business users to find out whether they need point-in-time recovery for these databases, or if the users are okay restoring to the last full backup if the server fails.

---

How to Set Up Transaction Log Backups

You can set up transaction log backups using SQL Server’s built in Maintenance Plans, or you can choose to go a little more rocket science-y and use free tools to customize jobs in the SQL Server Agent. We’ll outline both here, the choice is up to you.

Whichever way you choose: consider backup compression. This feature is available even in Standard Edition from SQL Server 2008R2 forward, and will make your log backups faster and the files smaller.

How Frequently Should Your Log Backups Run?

The log backups need to run frequently enough that they beat your Recovery Point Objective. That means that if you can only lose fifteen minutes of data for a database, you want to run the backups even more frequently– perhaps every five minutes or less.

Running log backups every one minute is not unusual.

How to Set Up Log Backups with Maintenance Plans (the easy button)

This option is simpler, because it does not require knowing TSQL. First, open SQL Server Management Studio’s Object Explorer, and under Management -> Maintenance Plans, Select “New Maintenance Plan”

Name the Maintenance Plan “Transaction Log Backups”.

View the toolbox, and drag the “Back Up Database” task into the field.

Now double click the “Back Up Database” task to configure the log backup.

1. Set backup type to Transaction Log
2. Edit the databases option. Select all databases and check ‘ignore databases where the state is not online’. Click OK.
3. Click the ‘Destination’ tab and set up the backup location. Note: never back up to the SQL Server itself. Use a share or a UNC path to separate storage.
4. Click the ‘Options’ tab and enable backup compression if available.
5. Click OK

Now set up the schedule. Click the calendar button on the Agent job to get the scheduling pop-up.

To set up your schedule:

• Under frequency, change “Occurs” to Daily
• Under Daily frequency, set the number of minutes between each run of the job. Make sure this is more frequent than your recovery point objective.
• Leave “Starting at” and “Ending at” to the default values. You want log backups to run consistently– it’s fine if they overlap with full backups or other maintenance jobs.
• Click OK

Click the “Save” button to save the Maintenance Plan and close it.

Now make sure the job is running successfully. Go to the SQL Server Agent in Object Explorer and find the job. Right click and “View History”.

If the job is failing, highlight the row and review the detailed errors to find out what the problem is. In this example, I am trying to run log backups against databases where no full backup has been taken– and SQL Server is letting me know that just doesn’t work. To solve that, I also need to set up a maintenance plan to take regular full backups.

Once you have your maintenance plan running successfully, set it so that it will notify you if there’s a problem. Right click on the SQL Server Agent Job and select ‘Properties’.

Go to the notifications tab and set the job to notify an operator if it fails.

Not sure what an operator is? Learn more here.

How to Set Up Log Backups with Free Tools (Getting Rocket Science-Y)

Ola Hallengren offers free scripts to set up and manage SQL Server backups using SQL Agent Jobs.

These scripts are widely used and allow you to configure the following for your log backups (among other things):

• @BackupType: ‘LOG’ – This is what tells the backup procedure to run a transaction log backup, not a full or differential.
• @Directory: Make sure to set this. The default value for this might leave you backing up to the SQL Server itself if you didn’t configure that when you installed the procedure, and that’s big trouble.
• @Compress: this can be integrated with third party tools if you use them- there are extra parameters for throttling and backup encryption features supported by those tools.
• @Checksum: This tells SQL Server to check page checksums while backing up. It can eat extra CPU and it doesn’t replace running CHECKDB, but if you’d like an extra layer of protection  you might like this option.
• @Verify: this is the RESTORE WITH VERIFY ONLY command. Note that it doesn’t prove that a restore will fully work, and it does take time to run.
• @ChangeBackupType: If a log backup cannot be run against the database, this tells the job to change the backup type. This could be risky in environments with very large databases (surprise! a full backup is running!), but if you have an environment where smaller databases may be added automatically during the day and immediately require protection, this feature could come in very useful.

Protip: If you’re interested in learning about advanced things you can do by seeing “code recipes”, scroll to the bottom of Ola’s page and read his example scripts.