Wortz, Charles
CWortz at tea.state.tx.us
Fri Feb 21 13:34:06 CST 2003
Francisco, How frequently you backup is dependent on how valuable is the lost data. If you can afford to lose a day's worth of data, or if you can easily recreate the day's worth of data, then you backup daily. If you can afford to lose an hour's worth of data, or if you can easily recreate the hour's worth of data, then you backup hourly. If you cannot afford to lose any data, then you mirror your database. Charles Wortz Software Development Division Texas Education Agency 1701 N. Congress Ave Austin, TX 78701-1494 512-463-9493 CWortz at tea.state.tx.us (SELECT * FROM users WHERE clue > 0) -----Original Message----- From: Francisco H Tapia [mailto:my.lists at verizon.net] Sent: Friday 2003 Feb 21 13:19 To: dba-SQLServer at databaseadvisors.com; sswug-sql2k at topica.com Subject: [dba-SQLServer]Rolling back a transaction log? Well it finally happened... We have a Complaint database that runs in conjunction with a goldmine database. The Goldmine database tracks outgoing calls made by our company to customers in order to track sales leads and now complaints. Since 4/1/2002 we have not had a database error or hiccup until now. The company's official DBA, while working on a development database on the production server (yes, that's right) inadvertently wiped out my database instead of his test one this morning, The users of the Complaint db suddenly began to complaining that there were no records, and upon checking I found this to be the case. I backup every night, and I have the log file backup when it reaches 60%. BUT. I did not have *ANY* protection for the moments before the wipe out. Initially I panicked about not being able to kick the users out quickly enough... I didn't bother to *remember* that I had a Kill All Users In Db script. So about 10 minutes later (after kicking all the users out) I restored the database back to last log backup, but that was not good as it had the transactions that wiped out the database. SO I had to restore to last nights copy officially killing all entries from 10am and prior. :( I've secured my script for killing Active Users in the DB. And My boss knows *who* wiped out the database, in fact I made sure he knew as soon as it happened ... maybe that's not a good political move, but I'm in charge of the db. Now the question is... Since the log file is only 1meg long and on average it doesn't backup the log for perhaps every 2 to 3 days... (I do make a full backup every night). I suppose I could manage the backups to include incremental changes every hr, so that as little data is lost? What do you guys suggest? -Francisco http://rcm.netfirms.com