I have a client with a very basic single server setup that includes a small SQL Server Express database.

I recently configured Symantec Backup Exec 2010 Quickstart edition for them. This is the free, feature-limited OEM version of Backup Exec that does not include the SQL Server Agent (or any application agent). However, it does support VSS via the Advanced Open File Option (AOFO). In this situation, I've always configured a scheduled task to dump the databases and back up the dumps so I can be sure I have a consistent backup.

However, after running an initial test job of the whole box with AOFO enabled, I noticed that it happily backed up the SQL data files, including the MDF/LDF files, and simply gave me a very softly worded "recommendation" that I might want to consider purchasing the SQL Server Agent as it had detected SQL Server data. This is surprised me on two fronts:

  1. It was my understanding that Backup Exec automatically excludes MDF/LDF files from flat-file backups using the Active File Exclusion or 'AFE' feature unless you manually disabled this via a registry key. My understanding was that this was because it's always a bad idea to back them up directly. The fact they weren't excluded was therefore odd and perhaps intentional.
  2. The lack of any serious warning stating that the SQL data may be inconsistent, unrestorable or whatever. Just a polite recommendation regarding the SQL Agent.

This led me to wonder, is it actually safe to back up the SQL Server data directly if you're using VSS (via AOFO)? After all, that would presumably mean the SQL Server VSS Writer is called to ensure the data files are app-consistent before the snapshot is taken. Backup Exec seems to have 'let' me do it, despite having recognised the SQL data as such.

I appreciate that using a dedicated SQL Agent provides a number of benefits, but purely on the question of taking a basic, consistent, restorable backup, is this safe?

There seems to be very little in terms of a definitive answer to the question, with some conflict. Obviously in the absence of one I'll be taking the tried and tested route, but it's got me thinking.

Here's some of what I've found so far:

  • 2
    From what I understand, you should have crash-level recovery of the databases. In that it'll be a bit better but roughly the same as if the power cord was yanked out of the server. So it's better than nothing, certainly! – Mark Henderson Jul 23 '15 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.