Full Recovery Model Without Log Backups
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:
- 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.
- 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.
- Set backup type to Transaction Log
- Edit the databases option. Select all databases and check ‘ignore databases where the state is not online’. Click OK.
- 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.
- Click the ‘Options’ tab and enable backup compression if available.
- 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.
6 Comments.
I’ve noticed an issue with the logic of this script.
Line 194:
b.backup_finish_date <= DATEADD(dd, -7, GETDATE())
The script uses the backup finish date to perform a logic test. I'm still getting my head around it, but it seems that it's testing to see if there are any databases where logs haven't been backed up in seven days. It does this based on the value(s) of backup_finish_date in msdb.dbo.backupset. But it gets tripped up if the only log backups that have ever been performed happen to be under 7 days old. For example:
Today is the 25th of May. I've performed a handful of log backups as part of testing something else. If I run the code from this script manually, as such:
SELECT database_name, backup_finish_date FROM msdb.dbo.backupset
where
not exists
(SELECT * FROM msdb.dbo.backupset b
WHERE database_name = 'Site 1 backup'
AND b.type = 'L'
AND b.backup_finish_date <= DATEADD(dd, -7, GETDATE()));
I get this result:
Site 1 Database 2012-05-25 09:39:50.000
Site 1 Database 2012-05-25 09:45:11.000
Site 1 Database 2012-05-25 09:45:12.000
Site 1 Database 2012-05-25 09:49:10.000
Site 1 Database 2012-05-25 09:50:08.000
Clearly backups have been performed. But because of the script's logic, it returns a result set, which the script then reports back as not having a recent log backup.
I hope I'm articulating this properly – I'm new to SQL and trawling through this code is a bit more in-depth than I'm used to.
In short, the script works well under these conditions:
1. A log backup has never, ever been performed
2. A log backup has been performed, but not in the last seven days (I'm yet to thoroughly test this one, but I suspect it is true)
Where it gives inaccurate results is when all of the log backups *EVER* performed are within the last 7 days. I hope this makes sense. Are you able to reproduce this in your environment?
I am running into a similar problem. I had a DB that log backups were not being performed. I added the log transactions to my backup procedures and they backed up just fine. However, sp_Blitz still flags this item as not having a log backup in the last 7 days.
I think the solution might be to use the start date instead of backup_finish_date, or to perhaps use both with some sort of comparison operation. I haven’t had time to look at trying to change the code to fix this. For now, it’s enough for me to know that for a seven day period after fixing any issues with log backups, the script will report a false alarm.
I’m hoping that the script’s authors will chime in. They know much more about this stuff than I do!
I think changing the line in the sub-query that checks the backup_finish_date from:
AND b.backup_finish_date = DATEADD(dd, -7, GETDATE())
will solve the problem. The original version looks for backup sets where records do NOT EXIST having backup_finish_date more than 7 days ago. What it really needs to check for is backup sets where the backup_finish_date does NOT EXIST within the last 7 days.
I’m not sure what happened to my comments but what showed up does not match up with what I entered.
The code section was supposed to read –
From:
“AND b.backup_finish_date = DATEADD(dd, -7, GETDATE())”
I guess the [less than] and [greater than] got treated as html tags.
Still didn’t work (I need to learn HTML some day). Basically what I was trying to say is to change the [less than =] in front of the DATEADD to [greater than =]