Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
swnz
Starting Member
2 Posts |
 Posted - 2004-06-05 : 02:18:28
|
Hi Everyone,We currently have a "student management" database that contains the academic records of all students at our institution. This database endures 1000's of update transactions per hour, and is currently set to the full recovery mode.Each night we use Veritas Backup Exec 9.1 w/ the SQL agent, to run a full disaster recovery backup in copy mode, which is taken off site. On Fridays, we run a full backup, with incremental backups occuring each weekday.My understanding is that a full backup does not truncate the transaction logs. Would our weekday incremental backups be doing this?Also, my concern is that if we experience a catastrophic database server failure during the day, that potentially, we would only be able to restore up to the last nightly backup that occured. Should we be copying the transaction file to a safe location regularly during the day? Any recomendations most appreciated  |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-06-05 : 03:00:48
|
| In Full recovery mode, the way to truncate the transaction logs is to run a transaction log backup (BACKUP LOG ...). I presume when you said you were doing incremental backups daily that you were referring to DIFFERENTIAL backups. I would encourage you to do backups more frequently such as a FULL backup every night a DIFFERENTIAL mid-day, and LOG backups in between, perhaps hourly.Regardless, as long as all your backups from the last FULL to the most recent are good, then you can recover to any point in time ASSUMING that the first thing you do when you have a crash is to do a LOG backup before attempting to restore anything. You have to get the transaction log backed up from whatever previous backup occurred up to the point of crash in order to be able to restore up to the point of crash.In general, you are correct that you can only restore up to the most recent backup. If you are unable to backup the log after the crash, then you're stuck with whatever last backup you had before that. If you're still concerned about up-to-the-second data, you might need to consider doing replication to another server.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
 |
|
|
|
|
|
|
|
Recommendation of backup approach