Best Method for SQL Backup

[rbax]

We recently migrated a bunch of databases to a SQL 7.0 server. I was wondering what the best method for backup is. We use Backup exec 8.6 to do remote backups. We get backups of the databases but not the transaction logs as they are always open. The databases do have a number of transactions each day. I'm thinking of using a SQL maintenance plan to backup the transaction logs but am wondering how often to do the backup and how long I need to keep the backup of transaction logs before deleting the older backup copies of the logs? I'm thinking backup the transaction logs every 2 to 3 hours and delete backup log files over 24 hours old. We do a full backup each night. Do I need to keep backup transaction log files longer than 24 hours?
Thanks,
Rick Baxter

 

[ecobb]

That really depends on how far back you want to be able to recover. If something happens to your database info right before the nightly backup, and you backup bad info, and your maintenance plan deletes all transaction logs until you find it the next morning, the best you'll be able to do is restore from the night before last's backup, not being able to go past that because you no longer have the logs. You've lost over a day's worth of data. This may seem far fetched, but it does happen.

I have all of my SQL Servers backing up each of their databases every 4 hours, while backing up the transaction logs every 30 minutes in between. I then keep the backups for 3 days.

As you already mentioned, all of this can be done with a Maintenance Plan. By using this method, SQL Server will be able to perform the backups without having to shut down the databases, it can even backup databases (and logs) while they are being used.

If you only want to keep 24 hours of transaction log backups, I would suggest backing up your database more than once a day. It will make recovery quicker too, should you ever need it.

Hope this helps!

 

[SQLBill]

Something else to consider, I use the native SQL BACKUP commands to backup my databases to hard drive and then use Veritas BE 8.6 to COPY those files to tape.

-SQLBill

 

[rbax]

Thanks, for the response, I'm somewhat limited with disk space so I cannot backup the databases to disk multiple times each day. I should have no problem keeping a couple days worth of transaction logs so that is most likely my best option for now.
Thanks,
Rick

 

[SQLBill]

I only have three files on disk per database. I have one fullbackup, one diff, and one transaction file. My SQL Server backup commands overwrite those files each time the backup command runs. I have Veritas 'timed' to backup those files after they have finished.

So, even though I do multiple backups to disk every day I still only have three files on disk (per database).

-SQLBill