I tried running MSBackup, but it only created a 6.5 gig file out of 18.5Gigs.. can it really get that much compression? Im sure I set it to backup everything..
You mean NTBackup? remember SQL databases can compress down a lot, especially if they grow and shrink a lot over time and havent been compacted (they only grow, never shrink).
What program would you suggest we try for nightly backups to an external harddrive?
NTBackup. The component that comes with the OS and is capable of utilizing the volume shadow service.
As for the SQL issue, yea I realize there are custom ways of backing up DB's, but this is the way my supervisor wants to do it.
Custom ways? SQL server has backup components built into it. If you want to backup the databases individually than you could just use SQL server's built in capabilities to backup directly to the external hard drive (no need for any additional utilities).
Yes, you need special tools in order to backup open files on Windows systems. Also you shouldn't just backup the MSSQL DB files as normal files, most backup tools have plugins to handle backing SQL datastores properly.
Pay close attention to this. Backing up the SQL data files of online databases is a sure-fire way to get yourself in to trouble if you ever need to do a recovery. The only time that the files should be backed up directly in this fashion would be if they are detached from the server first. And keep in mind that it wont back up everything that's related to the database (i.e. security privilages).
Did you ever try restoring them?
Yes, part of your backup stratagy should be to restore the files in a test enviroment to ensure you're actually getting the data backed up correctly (even restoring to MSDE installed on your desktop would be okay). It's pointless to waste your time running backup jobs unless you know that you'll be able to restore them if-and-when a failure occurs.
NTBackup comes with the OS and should be capable of doing all your backup needs on a stand alone server. It's a component that comes with the OS and wont cost you any extra. If you were concerned with more options for recovering single databases I also suggest using SQL server's built-in backup utility. But it should only be used in addition to full backups, not as a replacement.