Actual Emails: The Customer Doesn't Want Full Recovery Mode

Got this question in an email.  I'll paraphrase.

William 

Hoping you can advise me on best way to handle this. 

My client doesn’t want to use SQL backups. Instead, they are using VM and a third party backup software by choice.

Without a DBA on staff, they would prefer to deal with SQL the less the better.

They do not plan on doing any transaction logs and are not concerned with restore from point of failure.

... 

Goal would be to not have transaction logs or somehow keep those logs small.   

Colleague

Here's how I replied.

So, the first question to ask a client who doesn't want transaction log backups is, "why?" 

The transaction log is not something to be afraid of. Backing up the transaction log is a simple process and clears out the log. Backup the log at regular intervals (somewhere between 15 and 60min is typical). Then, you get all the advantages of point-in-time recovery, so if you have a 5:01pm disaster, you could restore all the way to 5:00pm, for example. Plus, full recovery mode is required for most SQL-based HA/DR scenarios.

Using a third party backup software is fine, but they must have purchased the SQL plugin.  This is usually an extra cost add-on for whatever backup license they bought.  You CANNOT backup an .mdf file with a file system backup and expect that to work. SQL MUST BACKUP SQL. 

That SQL plugin does exactly that for the third party software - sends commands to SQL to perform a backup. It's fine if they don't want to use sql maintenance plans or jobs to perform the backups, but they must use the sql plugin for any 3rd party enterprise backup software.

With a database in full recovery mode, you must perform full and transaction log backups, or the log will grow.

Typically, you do a nightly full and hourly tran log backups. The reason people get scared/confused/frustrated with transaction logs is that they don't realize you are supposed to back up the log (which empties it) regularly. As in, hourly at most. If they want the log to stay small, back it up every 15 minutes. BackupExec can do that. 

If they are really stubborn about not wanting to use all the DR capability that they paid for, putting the database in simple mode is fine. Just full sql backups are needed then. In simple mode, transactions aren't stored in the log after they commit. A transaction commits, there's a checkpoint, and then it is wiped from the log. A simple recovery mode database will have its transaction log grow to the size of the biggest single concurrent set of transactions at any given time. 

Never truncate a log file. Perform a log file backup, which will empty the file. The file may still be large though it is empty, in this case if you absolutely need the space back, you can shrink the log file. Never shrink a data or log file on a schedule. It creates fragmentation which hurts performance. 

Let me know if you have any questions! 

-William