<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6343.0">
<TITLE>RE: [dba-SQLServer]Rolling back a transaction log?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">John,</FONT>
</P>
<P><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">There is not much you can do if you have a DBA that intentionally deletes both copies of a file. But the mirror gives a real-time backup which may solve half of Francisco's problem. The other half will have to be taken care of by the DBA's boss. Unless you only have one server, even a bloody idiot knows better than to have both development and production on the same server.</FONT></P>
<P><B><FONT COLOR="#0000FF" FACE="Arial">Charles Wortz</FONT></B>
<BR><FONT COLOR="#0000FF" FACE="Arial">Software Development Division</FONT>
<BR><FONT COLOR="#0000FF" FACE="Arial">Texas Education Agency</FONT>
<BR><FONT COLOR="#0000FF" FACE="Arial">1701 N. Congress Ave</FONT>
<BR><FONT COLOR="#0000FF" FACE="Arial">Austin, TX 78701-1494</FONT>
<BR><FONT COLOR="#0000FF" FACE="Arial">512-463-9493</FONT>
<BR><FONT COLOR="#0000FF" FACE="Arial">CWortz@tea.state.tx.us</FONT>
<BR><FONT COLOR="#000000" FACE="Arial">(SELECT * FROM users WHERE clue > 0)</FONT>
</P>
<P><FONT FACE="Arial"></FONT> <FONT SIZE=1 FACE="Tahoma">-----Original Message-----</FONT>
<BR><B><FONT SIZE=1 FACE="Tahoma">From: </FONT></B> <FONT SIZE=1 FACE="Tahoma">dba-sqlserver-admin@databaseadvisors.com [</FONT><A HREF="mailto:dba-sqlserver-admin@databaseadvisors.com"><U><FONT COLOR="#0000FF" SIZE=1 FACE="Tahoma">mailto:dba-sqlserver-admin@databaseadvisors.com</FONT></U></A><FONT SIZE=1 FACE="Tahoma">] </FONT><B> <FONT SIZE=1 FACE="Tahoma">On Behalf Of</FONT></B> <FONT SIZE=1 FACE="Tahoma">John W. Colby</FONT></P>
<P><B><FONT SIZE=1 FACE="Tahoma">Sent: </FONT></B> <FONT SIZE=1 FACE="Tahoma">Friday 2003 Feb 21 13:47</FONT>
<BR><B><FONT SIZE=1 FACE="Tahoma">To: </FONT></B> <FONT SIZE=1 FACE="Tahoma">dba-sqlserver@databaseadvisors.com</FONT>
<BR><B><FONT SIZE=1 FACE="Tahoma">Subject: </FONT></B> <FONT SIZE=1 FACE="Tahoma">RE: [dba-SQLServer]Rolling back a transaction log?</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Would mirroring really help if someone intentionally deletes it? Wouldn't the mirror be deleted as well?</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">John W. Colby</FONT>
<BR><FONT SIZE=2 FACE="Arial">Colby Consulting</FONT>
<BR><A HREF="file://www.ColbyConsulting.com"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">www.ColbyConsulting.com</FONT></U></A>
</P>
<P><FONT SIZE=2 FACE="Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=2 FACE="Arial">From: dba-sqlserver-admin@databaseadvisors.com</FONT>
<BR><FONT SIZE=2 FACE="Arial">[</FONT><A HREF="mailto:dba-sqlserver-admin@databaseadvisors.com]On"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">mailto:dba-sqlserver-admin@databaseadvisors.com]On</FONT></U></A><FONT SIZE=2 FACE="Arial"> Behalf Of Wortz,</FONT>
<BR><FONT SIZE=2 FACE="Arial">Charles</FONT>
<BR><FONT SIZE=2 FACE="Arial">Sent: Friday, February 21, 2003 2:34 PM</FONT>
<BR><FONT SIZE=2 FACE="Arial">To: dba-sqlserver@databaseadvisors.com; sswug-sql2k@topica.com</FONT>
<BR><FONT SIZE=2 FACE="Arial">Subject: RE: [dba-SQLServer]Rolling back a transaction log?</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Francisco,</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">How frequently you backup is dependent on how valuable is the lost data.</FONT>
<BR><FONT SIZE=2 FACE="Arial">If you can afford to lose a day's worth of data, or if you can easily</FONT>
<BR><FONT SIZE=2 FACE="Arial">recreate the day's worth of data, then you backup daily. If you can</FONT>
<BR><FONT SIZE=2 FACE="Arial">afford to lose an hour's worth of data, or if you can easily recreate</FONT>
<BR><FONT SIZE=2 FACE="Arial">the hour's worth of data, then you backup hourly. If you cannot afford</FONT>
<BR><FONT SIZE=2 FACE="Arial">to lose any data, then you mirror your database.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">Charles Wortz</FONT>
<BR><FONT SIZE=2 FACE="Arial">Software Development Division</FONT>
<BR><FONT SIZE=2 FACE="Arial">Texas Education Agency</FONT>
<BR><FONT SIZE=2 FACE="Arial">1701 N. Congress Ave</FONT>
<BR><FONT SIZE=2 FACE="Arial">Austin, TX 78701-1494</FONT>
<BR><FONT SIZE=2 FACE="Arial">512-463-9493</FONT>
<BR><FONT SIZE=2 FACE="Arial">CWortz@tea.state.tx.us</FONT>
<BR><FONT SIZE=2 FACE="Arial">(SELECT * FROM users WHERE clue > 0)</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">-----Original Message-----</FONT>
<BR><FONT SIZE=2 FACE="Arial">From: Francisco H Tapia [</FONT><A HREF="mailto:my.lists@verizon.net"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">mailto:my.lists@verizon.net</FONT></U></A><FONT SIZE=2 FACE="Arial">] </FONT>
<BR><FONT SIZE=2 FACE="Arial">Sent: Friday 2003 Feb 21 13:19</FONT>
<BR><FONT SIZE=2 FACE="Arial">To: dba-SQLServer@databaseadvisors.com; sswug-sql2k@topica.com</FONT>
<BR><FONT SIZE=2 FACE="Arial">Subject: [dba-SQLServer]Rolling back a transaction log?</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Well it finally happened... We have a Complaint database that runs in</FONT>
<BR><FONT SIZE=2 FACE="Arial">conjunction with a goldmine database. The Goldmine database tracks</FONT>
<BR><FONT SIZE=2 FACE="Arial">outgoing calls made by our company to customers in order to track sales</FONT>
<BR><FONT SIZE=2 FACE="Arial">leads and now complaints. Since 4/1/2002 we have not had a database</FONT>
<BR><FONT SIZE=2 FACE="Arial">error or hiccup until now. The company's official DBA, while working on</FONT>
<BR><FONT SIZE=2 FACE="Arial">a development database on the production server (yes, that's right)</FONT>
<BR><FONT SIZE=2 FACE="Arial">inadvertently wiped out my database instead of his test one this</FONT>
<BR><FONT SIZE=2 FACE="Arial">morning, The users of the Complaint db suddenly began to complaining</FONT>
<BR><FONT SIZE=2 FACE="Arial">that there were no records, and upon checking I found this to be the</FONT>
<BR><FONT SIZE=2 FACE="Arial">case. I backup every night, and I have the log file backup when it</FONT>
<BR><FONT SIZE=2 FACE="Arial">reaches 60%. BUT. I did not have *ANY* protection for the moments</FONT>
<BR><FONT SIZE=2 FACE="Arial">before the wipe out. Initially I panicked about not being able to kick</FONT>
<BR><FONT SIZE=2 FACE="Arial">the users out quickly enough... I didn't bother to *remember* that I had</FONT>
<BR><FONT SIZE=2 FACE="Arial">a Kill All Users In Db script. So about 10 minutes later (after kicking</FONT>
<BR><FONT SIZE=2 FACE="Arial">all the users out) I restored the database back to last log backup, but</FONT>
<BR><FONT SIZE=2 FACE="Arial">that was not good as it had the transactions that wiped out the</FONT>
<BR><FONT SIZE=2 FACE="Arial">database. SO I had to restore to last nights copy officially killing</FONT>
<BR><FONT SIZE=2 FACE="Arial">all entries from 10am and prior. :(</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">I've secured my script for killing Active Users in the DB. And My boss</FONT>
<BR><FONT SIZE=2 FACE="Arial">knows *who* wiped out the database, in fact I made sure he knew as soon</FONT>
<BR><FONT SIZE=2 FACE="Arial">as it happened ... maybe that's not a good political move, but I'm in</FONT>
<BR><FONT SIZE=2 FACE="Arial">charge of the db. Now the question is... Since the log file is only</FONT>
<BR><FONT SIZE=2 FACE="Arial">1meg long and on average it doesn't backup the log for perhaps every 2</FONT>
<BR><FONT SIZE=2 FACE="Arial">to 3 days... (I do make a full backup every night). I suppose I could</FONT>
<BR><FONT SIZE=2 FACE="Arial">manage the backups to include incremental changes every hr, so that as</FONT>
<BR><FONT SIZE=2 FACE="Arial">little data is lost? What do you guys suggest?</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">-Francisco</FONT>
<BR><A HREF="http://rcm.netfirms.com"><U><FONT COLOR="#0000FF" SIZE=2 FACE="Arial">http://rcm.netfirms.com</FONT></U></A>
</P>
</BODY>
</HTML>