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


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


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.   


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) and there is no problems. 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. 


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 during

business hours. 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