SQL Agent Benefits

[David_Ray]

My organization purchased Symantec Backup Exec Sql Agents for some of our Microsoft SQL Servers. I don't understand how the SQL Agent works on a SQL Server.
If Symantec SQL Agent can run full, diff, and log backups, do I stop running those backups in SQL Enterprise Manager?
If I do, I've noticed that a full backup of one of our small databases takes 22 seconds with Enterprise Manager but takes over 5 minutes with Symantec.
Also, if I have problems where I need to restore the database to a certain point, isn't it going to be much faster if the log file that I'm restoring from is on the scsi disk rather than on the remote backup exec server?
If I continue to use SQL Enterprise to backup the database as well as Symantec, what are the system performance issues with having 2 systems run backups?
Basically what I'm asking is what are the benefits to having Symantec backing up my data? I only see 2 benefits - 1) to have a copy of the backup on a separate server (I can purchase the windows agent for a whole lot less and still do this) and 2) restoring may be easier with Symantec than with SQL Enterprise. The frustrating part of using this Symantec is that I haven't found any good documentation by Symantec on this. Any insight/help is appreciated.

[Keith_Langmead]

Personally I think both the Backup Exec and Enterprise Manager methods have benefits depending on your situation, which is why I run both on all our SQL Servers, to give me both options depending on the situation.

Backup Exec - The biggest benefit here is disaster recovery, since the backups presumably on tape which is then taken off-site, if the worst comes to the worst you can still recover your system. Additionally, if the complete server dies, it's much faster to recover the complete system at once then to have to re-install the system, and then restore each of the databases manually. To my knowledge with the Enterprise Manager you have to restore each database individually, which would obviously take a long time you have multiple db's.

Enterprise Manager - Much faster for recovering data since the backup information is generally stored locally (and you don't have the hassle of switching tapes etc), which makes it ideal for dealing with software related problems with an individual database. For instance someone makes a mistake on the database, some data is entered incorrectly etc, you only need to restore that one db, so it's handy to be able to recover it extreamly quickly.

As for the performance hit on the server I'm not sure to be honest, the databases we use are fairly small, so the time to do a EM backup is minimal, but on a larger system it might become an issue.

Personally I'd go for a full Backup Exec backup each night to tape which is taken off-site, and then also do EM backups either once per day, or possibly several times throughout the day, to allow better roleback if a mistake or whatever happens.

Keith

[Hywel_Mallett]

If you have a large database, and use EM backups to backup to file, then back these up to tape, you need a large amount of disk storage for the EM backups.

[David_Ray]

We do tape backups only on a couple of our databases. We have an onsite NAS device that we copy the databases to, and we have plans to setup an offsite server to also copy databases to. We've gotten tired of the hassle of having to deal with tape backups and decided to go this route instead.
Thanks guys for the input-I appreciate it.

[Deepali_Badave]

Hello,

We suggest you to refer the BE admin guide for more information:

http://ftp.support.veritas.com/pub/support/products/Backup_Exec_for_WindowsNT/279401.pdf

Regards,

[Peter_Ludwig]

I in our organisation quit using the SQL agent at all. I just back up the backupfiles with the normal agent.
It is said that if you have database people in your company, you should leave the backup and restore work to them and backup and restore only simple files (and as a backup person not caring about databases at all).

greetings
Peter

[priya_khire]

Hello,

The SQL Agent provides the following benefits:

■ Database, transaction log, differential, and filegroup backups, as well as databaserecovery and replacement.
■ An automated restore of the master database.
■ The Intelligent Disaster Recovery option, which automates the disaster recovery
process of SQL 2000 or SQL 7.0, depending upon which SQL version is installed.
In SQL 2000 installations, the SQL Agent provides support for:
■ Backups of multiple instances.
■ For use with the Symantec Backup Exec for Windows Servers - Advanced Disk-based Backup Option (ADBO) and the Advanced Open File Option (AOFO). ADBO and AOFO are separate, add-on components of Backup Exec. The use of ADBO and AOFO can
reduce both restore time and backup impact on the server.
■ Standby database. If the primary SQL server fails, or is shut down for maintenance, another database called a standby database can be brought online. The standby database contains a copy of the primary server's databases so that users can continue to access the database even though the primary server is unavailable.

When the primary server is available again, the changes on the standby database
must be restored back to the primary server or the changes will be lost. The
databases on the primary server should then be backed up and restored on the
standby database again.

Backup Exec provides a backup option that enables you to put the database in
standby mode when the log file backup completes, and a recovery completion state of Leave database read-only and able to restore additional transaction logs to
create and maintain a standby database.
■ Database Consistency Checks (DBCC) for each backup and restore job, including a fast database consistency check of only the physical consistency of the database.
■ Full, bulk-logged, and simple recovery models. The simple recovery model is similar to setting the truncate log on checkpoint option in SQL 7.0. With the simple recovery model, copies of the transactions are not stored in the log file, which prevents transaction log backups from being run. Therefore, you can recover the database to the point of the last backup, but you cannot restore the database to the point of failure or to a specific point in time.
■ Restores of transaction logs to a specific point in time or to a named transaction
when log marks are used.
In SQL 7.0 installations, the SQL Agent provides support for:
■ Database Consistency Checks (DBCC) before and after each backup job.
■ Restores of transaction logs to a specific point in time.