SQL - Backup Procedure

[GysE]

Hi,

We have to implement a backup procedure for a fairly big (50GB) SQL database and we come up with the following. Note, data is critical for the business:
Full Backup - Sunday night.
Differential Backup - Every evening, except Sunday night.
Transaction Log Backups - Hourly, except during a window that we keep open to do db maintenance (Re-indexing, etc.)

We are taking all backups straight to DLT. We don't keep any backups on disk.

My concerns/question:
1. What is your opinion on the above backup procedure?
2. Is it feasible to backups straight to DTL.
3. Is it correct in saying that differential backup saves physical space, but it takes long to backup.
3. Will the SQL/Tape Device be intelligent enough to know where and how to place the backups to tape.

Thanks
Gys

 

[uncLez]

Hi,
I am no SQL admin GURU but I do have some experience on the subject.

Your last questions first
You can either backup to different tapes, or have the DLT change tape whenever it is full, both options are perfectly usable.

However, I myself would prefer to have dumps on disk in the server... Especially for mission critical application data, I would do this, since it can save you a couple of hours restore time, if the dump on disk is good.

Backups straight to DLT is perfect. For recent versions of SQL there are open database backup options. I am not too sure about the time differential backups take. Can't hepl ya there...

Other than that your plan seems pretty well laid out.

 

[SQLBill]

I am backing up 172+ GB of data (one database). I do a transaction backup every 7 hours, a differential backup Saturday through Thursday night, and a full backup every Friday night (that's why I don't do a differential on Friday). I do backups straight to disk and then use Veritas Backup Exec to copy the backup files to tape.

My transaction log backups take a average of 12 minutes. The differential backup takes an average of 55 minutes. The full backup currently takes 6 hours to complete. (These are the times for backing up to disk only, it doesn't include the time going from disk to tape.)

-SQLBill