Are you backing up the .mdf and .ldf files directly?  These files are normally excluded from the backup by the Automatic File Exclusion technology.  See the document below.

http://www.symantec.com/business/support/index?page=content&id=HOWTO24057&actp=search&viewlocale=en_US&searchid=1294623343177

Even if you managed to back these files up, they may be such that they are not usable when they are restored.  There is no guarantee.

If you don't want to use the SQL Agent, the normal practice is to backup SQL databases to flat files using the backup capability built into SQL and then backup these flat files with BE.  Restore a database would involve restoring these flat files and then using them to restore the databases.  I am not sure if SQL Express has such backup and restore facilities.

Hence it is easier and better to use the SQL Agent to backup SQL databases.

From my findings the AFE settings have changed for R2. There SQL files are no longer excluded and you have to exclude them manually by changing the registry.

Why should the mdf and ldf files not be usable?

If BackupExec as a VSS requestor triggers Microsoft's VSS provider the VSS SQL writer service ensures that a consistent state is written to disk before the snapshot is created.

As already said for sure there are advantages for large databases if you are using the SQL agent, but I don't see any reason why the files should be corrupted if backed up this way.

Then you have to assume that they are also corrupt if you are using Microsoft's backup software which relies on same technical components.

Regards,

harsi.

Why should the mdf and ldf files not be usable?

They could be in the midst of being written to.  If you backup the mdf and ldf files and find then unusuable, I don't think you are going to get much sympathy from Symantec Tech Support.  It is a question of how much you value your recovery.

Microsoft VSS will not use SQL writer for flat file backup. SQL writer will be used for DB backup triggered using a software like Backup Exec. Instead of flat file backup, a better approach would be to perform backup using SQL internal tools and then backup the "backup set" created by them. Although, SQL Agent should be your preferred approach because it makes management of these backup sets easy.

Thanks

Seems like you know the answer already, and Symantec is just milking you and the rest of us for more money...

That said, backing up the flat files and using VSS does not protect the databases completely.  Log files are not truncated automatically, I cannot recover the database in a consistent state (I've already tried), and I cannot verify the consistency of the database prior to the backup (if I wanted to do that)

However, if I stop the SQL services via a pre-script, and start it via a post-script, the backup and recovery works 100% just fine, and the recovery is equally so.  Of course I have to do a manual scheduled task of truncating the logs, which isn't hard to do, just hard to track in case backups didn't happen for some reason or another.

Will it make life easier, yes an agent will do that.  Is it worth the money?  Well how easy would you like to restore a database, hot?  Your answer will determine the outcome.  It may not be for you harsi, but it is for the other 95% of the world.

Hi,

Base it on your budget, what you want to accomplish in terms of backups/restores, and downtime in order to do them both.

It also depends on how big and important your SQL DBs are. If they are mission critical, from my side I'd go with the agent. If not, and your business can do without them for a while, do without them.

Laters!

If they would be large and mission critical I would not think about it and I would bought the agent similar to Exchange, AD, and VMware.

But buying the agent to be able to e.g. backup SEP database or HP SIM and that for multiple sites is something I have to think about.

What still irritates me is that you get no definitive answer if SQL agent is mandatory to ensure database integritiy on backup or only adds comfort and features.

From the message displayed in the backup log the latter is the case. Translating from German you can read:

The following backup sets contain Microsoft SQL data. Symantec recommends installation and use of the "Backup Exec Agent for Microsoft SQL" for better management of SQL data.

Cheers.

Hi harsi,

The agent allows you to do consistency checks before the backup, as well as after the backup completes. This will check whether or not your DBs are in a consistent state.

So yes...it checks consistency and health before, and after the backup, and adds a bunch of features, like utilising SQL 2008's compression amongst others.

We run HP SIM in a very large environment here, and back it up (unfortunately...and soon to be replaced!) by ARCserve 15, with an SQL agent. I'd do the same thing if we used BE 2010 R2 in the data center (which is what I want it replaced with!). For me it is peace of mind.

That said...why not use SQL to do SQL backups, and then back up the flat files using AOFO in BE?

For any application to be backed, there are API's provided by vendor. Exchange provides ESE, Oracle provides RMAN and SQL also has its own API's. These APIs are designed in a way that they provide complete recovery solutions. Flat file backup does not use these APIs, hence not the best way to go about. Backup Exec uses APIs provided by SQL to perform backups, if SQL Agent is used. As suggested before, if you want to save some money, use SQL internal backups and backup the backup set created thus. Flat file backup is not a recommended practice for obvious reaosns.

Although with simple recovery model, you can stop services and backup files, but then you dont have option of point in time recovery. If you are a kind of organization where a minute is worth million, you would rather not do that. So, you go for full recovery model and perform log backups for which SQL Agent is your way out. If you have larger recovery window, and stopping service or using VSS to backup files is fine with you, its definitely an option.

Applications provide all options, its for people to choose based on their requirements.

Thanks