What Are These Backup Settings All About?

I ran across a client the other day that had these Backup and Recovery options set like the picture below because it is defaulted this way. The Database Administrator didn’t know what they should configure them as so he left them alone. I find this is the case with a lot of options. For the most part leaving the defaults can be just fine, but other leaving others cause leave you missing out on some good features.

Let’s start from top to bottom.

Default backup media retention in days. Now the first things that comes to my mind is that “hey this is a cleanup job” SCORE! Thinking that maybe this will auto delete old backups. After all isn’t that what retention means? NOPE, not in this case.

In this case it’s just a number of days before that a backup media can be OVERWRITTEN. If the DBA goes to overwrite the media before those days it will give a warning message. You’ll note in every back up action you do the RETAINDAYS option is filled in. In this case it will always reflect to 90 now that we have changed it. In general, this a pointless option to me. I don’t normally OVERWRITE backup media. To me this was more relevant when Tapes were used and disk were harder to come by, so I leave it alone.

USE AdventureWorks2014 ; 
GO 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE ; 
GO 
EXEC sp_configure 'media retention', 90 ; 
GO 
RECONFIGURE; 
GO

BACKUP DATABASE [AdventureWorks2014] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016DEV\MSSQL\Backup\AdventureWorksDW2014.bak' WITH  RETAINDAYS = 90, NOFORMAT, NOINIT,  NAME = N'AdventureWorksDW2014-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GUI

Compress backup. This one is exactly what you think it is, no guessing here. Backup Compression is one I highly recommend changing from the default. Compression is a HUGE topic I will save for another time. But in short, the smaller the files the less space it takes up, less data stored means IO (and less data sent to your backup device) and therefore your databases back up and restore faster. Here is a great MDSN link to learn more about the benefits of backup compression. Backup compression is included in all editions of SQL Server since 2008 R2, so use it!

Recovery Interval (in minutes). Now this one I always thought meant Recovery Point Objective, in other words how much data am I willing to lose in minutes. I am partially right. According to MSDN, this option defines an upper limit on the time recovering a database should take. The SQL Server Database Engine uses the value specified for this option to determine approximately how often to issue automatic checkpoints on a given database.

This is an option I don’t change. I have yet to see a scenario where I want to override when SQL Server does a check point on the database by default.  There are times when I want to force a check point but it’s not something I am going to set a standard for. The only reason I have heard was to reduce IO on a data drive, but to me that’s at too high of a cost.

TSQL

USE AdventureWorks2014 ; 
GO 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE ; 
GO 
EXEC sp_configure 'recovery interval', 3 ; 
GO 
RECONFIGURE; 
GO