| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2003-01-10 : 18:44:12
|
| We have recently acquired LogExplorer. While it is a great product, our current backup procedure is prohibiting us from viewing the entire set of transactions and using the log as a sort of audit trail.We currently have a daily backup that causes the transaction log to be truncated. We do this so if the server goes down we can get the previous days backup and hopefully have all of the data before the crash or whatever. We need to maintain this type of capability.This makes it difficult to view other than current days transaction data.I am trying to figure out the best way and want to know if anyone else has some suggestions.One plan on the table is to stop truncating the log and to make differential backups of the transaction log (is this possible?). I would like to put it on an hourly basis per database. I know I could script such a job if the backup strategy is possible. This would maintain pretty much the same idea.We use TSM (I forget what its actual name is) to do the backups. It backups the database and the file system. So have SQL server backup to files would be acceptable.Or if we could do a daily transaction log diff backup.The main point being, I need to keep the entire transaction log intact.Any ideas? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-10 : 18:50:58
|
| You can not perform differential backups on transaction logs. I verified this by looking at the BACKUP LOG syntax (there is no WITH DIFFERENTIAL option). Why can't you just backup the database once a day and then backup the transaction log every x number of minutes? As long as you copy the files to a different location, you will be safe. We do it this way, plus we log ship to another site.I don't understand how your current solution would give you the data before the crash. Your backup would only contain the data that was in the database at the time of the backup and not anything afterwards. This is why backing up the transaction logs is very important. Please clarify this. |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-10 : 20:03:21
|
| The way it was explained by our current tech people (they are not SQL Server DBAs, they just handle all the NT operations stuff) they do a daily backup. Then if the server crashes (say the hard disk is wiped out, they will just use the day before; I guess this is acceptable to them, not to me and I'm working on moving all the SQL Server DBA responsibilities onto myself) they can get the transaction log off the crashed box (assuming the crash did not wipe out the disk system) and roll forward the transaction log to that point...The TSM agent that does that backup is a massive network tape library I think, haven't read much on it. Its supposed to support backups that are very current, depending on how much CPU usuage you can afford to consume during the backup...I'm just looking for others experiences, and suggestions for maintaining a transaction log that needs to be backed up at least once an hour only on a 18 hour basis (6am-9pm). Also I need to maintain the transaction log in one full peice so that I can browse it like an audit record (which seems to be the best way to store audit history for our applications--keeps the load off the database rather than maintaining audit history in tables) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-10 : 20:10:05
|
| We backup our transaction logs every 15 minutes. It works very well for us.As far as having all of the transaction logs in one file, you could specify the with noinit clause, so that it appends instead of overwrites. You probably should do a with init once a day though. Please see BOL for the specifics of with init and with noinit.Using the day before's backup is totally unacceptable to my organization. That means lots of data is lost if a restore has to occur and you are 23 hours after the last backup. I would lose my job if that happened. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-13 : 11:49:15
|
| so here's the proposal i'm working on... any thoughts? suggestions?--------------------------------------------------------------------------------Database Backup Strategy To Make Use Of The Transaction Log For Audit History--------------------------------------------------------------------------------Current Strategy: The databae backup occurs nightly. During the backup the transaction log is truncated. Backups are stored for 45 days before it is purged. Transaction log data is not readily accessible to the DBA and is no longer available after the 45 day purge barrier. This process supports the rolling forward of the transaction log from the previous days backup. If a crash occurs that is non disk fatal, the database can be restored from the previous day, the transaction from the day of the crash can then be applied to the restored database to its state before the crash. If their is a disk fatal crash on the database server, only the previous days data can be restored. This would require the current days work up to the crash to be re-inputed into the database.Proposed Strategy: The database backup occurs nightly and on a every time measure basis, as needed by the database application. High volume databases will need more backups than low volume ones. The full database backup will not truncate the transaction log. The backups that occur every time measure will not truncate the transaction log and will only backup a differential of the database. Backups are stored for 45 days before they are purged. Transaction log data is readily accessible to the DBA and is available after the 45 day purge barrier. This process supports the rolling forward of the transaction log from the previous differential backup that occurs by the set time measure. If a crash occurs that is non disk fatal, the database can be restored from the previous full backup and each time measure differential backup applied to the restored database. If their is a disk fatal crash on the database server, only the previous days data can be restored. This would require the current days work up to the crash to be re-inputed into the database. Though there is a performance penalty for having large transaction logs, the transaction logs could be truncated every 90 days after a full backup has been created and archived and is readily available to the DBA at all times. This would reduce the size of the log. Performance only becomes an issue when the log is very large, and with an average transaction log size of 1-2MB daily the 90 day transaction log checkpoint would keep the log size at an acceptable size.Proposed Strategy to Handle Fatal Disk Crashes: Using the aboved proposed strategy, the transaction log differential backups can be stored on a remote location (TSM, or network drive). This would provide access to the transaction log differential backups in case of a fatal drive crash on the database system. This process would allow the database to be restored from the previous days full backup and each transaction log differential backup applied to the restored database, thus restoring it to the last known good state. The time measure used for differential backups should be no longer than 60 minutes. This will provide the database with good data at the last hour of operation before the fatal disk crash. For more critical databases such as a financial system, the differential backup time measure should be much smaller. Databases that rely on other databases should have the same differential backup time measure to keep their data synchronized. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-13 : 12:28:17
|
| I think that you are confused between transaction log backups and differential backups. Transaction logs contain each transaction. Differential backups is a snapshot of the database and contains the all of the changes since the last full backup, but it does not have individual transactions. You can restore to a point in time with a differential backup. For any production system where the data is important, the transaction logs should be backed up. Differential backups can be performed as well (but should not be done instead of transaction log backups).Your strategy does not allow you to have access to the transactions unless you BACKUP LOG. You should never truncate the transaction log in a production environment if you need to restore to a point in time. You run the BACKUP LOG to backup the transaction log. After the backup completes, it will truncate the log for you. I would really rethink your backup strategy. You should be backing up the transaction logs, especially since you have purchased LogExplorer. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-13 : 17:34:05
|
| thanks, these are the things i'm trying to figure out ... until now the backups have been left to our do it all NT group ... well would my plan to do differential backups at certain times of days after a full backup ... suffice for providing recovery data for a fatal disk crash? currently our plan does not support this happening, and it has happened to other systems ... luckily just constant file systems ... |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-01-14 : 13:04:47
|
| Onamuji, have you looked at Lumigent's new product called Entegra? I saw a pretty impressive demo of it at PASS, and one of the things it handles is auditing of data changes. Of course it audits a TON of other things, too. Just thought you might find it handy. I think they have a demo version now available.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-14 : 13:28:34
|
| that is exactly what I'm looking for to handle audit data...however I still need to convince these guys that the data needs to be backed up more... after a conversation with one of them this morning they just shrugged off the differential backup idea as extra work they don't want to do... grrr ***frustration mounts as onamuji shuffles through his desk looking for his shotgun*** ... |
|
|
|
|
Transaction Log Backup Strategy