Should we backup SQL 2000 with ArcServe SQL agent or backup to file and then backup the file?

Question

We have SQL Server 2000 running on Windows Server 2003. We've recently moved to new hardware. We use ArcServe / Brightstor for backups. On the old server we used the SQL agent. We also have agents for file system backups.

We only have one server. SQL Server is set to simple recovery model. The old system was doing weekly full ("Database") backups and daily Differentials to tape via the SQL agent.

This might be a silly question but I'm wondering what we really gain by using the SQL agent other than added complication and memory use.

We have plenty of disk space. Is it a reasonable strategy to backup to disk using the standard SQL Server backup functionality and then simply backup those files using the file system agent? One advantage I see of doing that is that it would give us precise control of the time when the SQL backup occurs. With ArcServe doing it, it happens in the queue which includes other servers on our LAN.

We soon plan to have a second server and implement log shipping.

Thanks

Answer 1

As far as I'm concerned it's a case of "six of one and a half dozen of the other". There's no right or wrong way so do it using whichever method you're more comfortable with in terms of timing, resource usage, reliability of the backup, etc. I often use a combination of the two methods depending on the server and backup strategies in use.

Answer 2

Given that you have a pretty simple setup, you're not really gaining much with 3rd party backups over the built-in osql backup. @squillman does make a good point about things like compression and encryption. We have used ArcServe and Veritas in the past but now just use osql backup and GnuPG in our backup script to accomplish the same thing and have had good luck with it. We do miss out on object-level restore this way but can live without it.

It may be worth it to you to pay for the simplicity of having these and other features in the software, but I tend to look at from the opposite standpoint -- 3rd party software makes creating backups more intuitive, but can add complexity as well as another dependency when it comes to restoring/recovery.