What Are These Backup Settings All About

What Are These Backup Settings All About?

I ran across a client the other day who had the Backup and Recovery options set like the picture below because it defaults this way. The Database Administrator didn’t know how they should configure them so that he could leave 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 leaving others alone can leave you missing out on some good features.

Let’s start from top to bottom.

Default backup media retention in days

Now, the first thing that comes to my mind is: “Hey this is a cleanup job” SCORE! I'm 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 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 backup action you do the RETAINDAYS option is filled in. In this case, it will always reflect 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 the disk was 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

TSQL

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 checkpoint on the database by default.  There are times when I want to force a checkpoint 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

So, there you have it, three more options that may not be a mystery for some any longer.