Back up a minute...

Looking for suggestions on some SQL issues. Please comment.

SQL Backups have been an issue around here since we lost some data a little while ago. I've never really been satisfied with any of the immediate solutions we have for solving things. We can sort of do a poor man's replication by backing up servers to each other each night, but that doesn't really provide anything comprehensive. So here's what I'm proposing: We divide our databases into three tiers of importance.

Tier 1: Database is backed up each night, and three days of backups are kept on disk. Transaction log backups are taken every three hours from 10AM to 7PM and retained for three days.

Tier 2: Database is backed up each night, and three days of backups are kept on disk. No transaction log backups are taken.

Tier 3: The database is backed up weekly, and one backup is kept on disk.

Tape backups retain data for four weeks, so going to an older backup should not be an issue.

In addition to that, I'm also looking at how the file loads are done each night. There's SQL code out there that can be used to verify the existence of a file, before you do anything with it. I'm contemplating a couple of different approaches to this.

The first approach is to create a job that scans for certain file names every so often. (15 minute intervals, or so.) If one is found, then it can run the appropriate DTS package and load the file. Now, I could just use that code in each individual DTS package, but I like the idea of having centralized control of the load process. You could still run packages individually if you needed to, manually.

Also, there's a way to send SMTP messages from SQL server, without having SQL mail and all the messiness MAPI entails. But you have to have Collaboration Data Objects installed. CDO can be messy as well, or so I have heard. Working that into the master job control package would be nice as well, since any delay in loading the job could result in an automatic message to the admins, as well as the database owners. Cool ideas.

But seriously, any suggestions on any of the above?