<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [dba-SQLServer]Rolling back a transaction log?</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1141" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=165510520-21022003><FONT face=Arial color=#0000ff
size=2>Charles, </FONT></SPAN></DIV>
<DIV><SPAN class=165510520-21022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=165510520-21022003><FONT face=Arial color=#0000ff
size=2>>But the mirror gives a real-time backup which may solve half of
Francisco's problem.</FONT></SPAN></DIV>
<DIV><SPAN class=165510520-21022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=165510520-21022003><FONT face=Arial color=#0000ff size=2>Yea,
but these (mirrors) are for situations where a disk dies etc. If you
intentionally delete something, the mirror is supposed to immediately write that
delete to the mirrored drive as well (isn't it?).</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<P><FONT size=2>John W. Colby<BR>Colby
Consulting<BR>www.ColbyConsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
dba-sqlserver-admin@databaseadvisors.com
[mailto:dba-sqlserver-admin@databaseadvisors.com]<B>On Behalf Of </B>Wortz,
Charles<BR><B>Sent:</B> Friday, February 21, 2003 3:00 PM<BR><B>To:</B>
dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> RE:
[dba-SQLServer]Rolling back a transaction log?<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial color=#0000ff size=2>John,</FONT> </P>
<P><FONT face=Arial color=#0000ff size=2>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 face=Arial color=#0000ff>Charles Wortz</FONT></B> <BR><FONT
face=Arial color=#0000ff>Software Development Division</FONT> <BR><FONT
face=Arial color=#0000ff>Texas Education Agency</FONT> <BR><FONT face=Arial
color=#0000ff>1701 N. Congress Ave</FONT> <BR><FONT face=Arial
color=#0000ff>Austin, TX 78701-1494</FONT> <BR><FONT face=Arial
color=#0000ff>512-463-9493</FONT> <BR><FONT face=Arial
color=#0000ff>CWortz@tea.state.tx.us</FONT> <BR><FONT face=Arial
color=#000000>(SELECT * FROM users WHERE clue > 0)</FONT> </P>
<P><FONT face=Arial></FONT> <FONT face=Tahoma size=1>-----Original
Message-----</FONT> <BR><B><FONT face=Tahoma size=1>From: </FONT></B>
<FONT face=Tahoma size=1>dba-sqlserver-admin@databaseadvisors.com [</FONT><A
href="mailto:dba-sqlserver-admin@databaseadvisors.com"><U><FONT face=Tahoma
color=#0000ff
size=1>mailto:dba-sqlserver-admin@databaseadvisors.com</FONT></U></A><FONT
face=Tahoma size=1>] </FONT><B> <FONT face=Tahoma size=1>On Behalf
Of</FONT></B> <FONT face=Tahoma size=1>John W. Colby</FONT></P>
<P><B><FONT face=Tahoma size=1>Sent: </FONT></B> <FONT face=Tahoma
size=1>Friday 2003 Feb 21 13:47</FONT> <BR><B><FONT face=Tahoma
size=1>To: </FONT></B> <FONT face=Tahoma
size=1>dba-sqlserver@databaseadvisors.com</FONT> <BR><B><FONT face=Tahoma
size=1>Subject: </FONT></B> <FONT
face=Tahoma size=1>RE: [dba-SQLServer]Rolling back a transaction log?</FONT>
</P>
<P><FONT face=Arial size=2>Would mirroring really help if someone
intentionally deletes it? Wouldn't the mirror be deleted as well?</FONT>
</P>
<P><FONT face=Arial size=2>John W. Colby</FONT> <BR><FONT face=Arial
size=2>Colby Consulting</FONT> <BR><A
href="file://www.ColbyConsulting.com"><U><FONT face=Arial color=#0000ff
size=2>www.ColbyConsulting.com</FONT></U></A> </P>
<P><FONT face=Arial size=2>-----Original Message-----</FONT> <BR><FONT
face=Arial size=2>From: dba-sqlserver-admin@databaseadvisors.com</FONT>
<BR><FONT face=Arial size=2>[</FONT><A
href="mailto:dba-sqlserver-admin@databaseadvisors.com]On"><U><FONT face=Arial
color=#0000ff
size=2>mailto:dba-sqlserver-admin@databaseadvisors.com]On</FONT></U></A><FONT
face=Arial size=2> Behalf Of Wortz,</FONT> <BR><FONT face=Arial
size=2>Charles</FONT> <BR><FONT face=Arial size=2>Sent: Friday, February 21,
2003 2:34 PM</FONT> <BR><FONT face=Arial size=2>To:
dba-sqlserver@databaseadvisors.com; sswug-sql2k@topica.com</FONT> <BR><FONT
face=Arial size=2>Subject: RE: [dba-SQLServer]Rolling back a transaction
log?</FONT> </P><BR>
<P><FONT face=Arial size=2>Francisco,</FONT> </P>
<P><FONT face=Arial size=2>How frequently you backup is dependent on how
valuable is the lost data.</FONT> <BR><FONT face=Arial size=2>If you can
afford to lose a day's worth of data, or if you can easily</FONT> <BR><FONT
face=Arial size=2>recreate the day's worth of data, then you backup
daily. If you can</FONT> <BR><FONT face=Arial size=2>afford to lose an
hour's worth of data, or if you can easily recreate</FONT> <BR><FONT
face=Arial size=2>the hour's worth of data, then you backup hourly. If
you cannot afford</FONT> <BR><FONT face=Arial size=2>to lose any data, then
you mirror your database.</FONT> </P>
<P><FONT face=Arial size=2>Charles Wortz</FONT> <BR><FONT face=Arial
size=2>Software Development Division</FONT> <BR><FONT face=Arial size=2>Texas
Education Agency</FONT> <BR><FONT face=Arial size=2>1701 N. Congress
Ave</FONT> <BR><FONT face=Arial size=2>Austin, TX 78701-1494</FONT> <BR><FONT
face=Arial size=2>512-463-9493</FONT> <BR><FONT face=Arial
size=2>CWortz@tea.state.tx.us</FONT> <BR><FONT face=Arial size=2>(SELECT *
FROM users WHERE clue > 0)</FONT> </P><BR>
<P><FONT face=Arial size=2>-----Original Message-----</FONT> <BR><FONT
face=Arial size=2>From: Francisco H Tapia [</FONT><A
href="mailto:my.lists@verizon.net"><U><FONT face=Arial color=#0000ff
size=2>mailto:my.lists@verizon.net</FONT></U></A><FONT face=Arial size=2>]
</FONT><BR><FONT face=Arial size=2>Sent: Friday 2003 Feb 21 13:19</FONT>
<BR><FONT face=Arial size=2>To: dba-SQLServer@databaseadvisors.com;
sswug-sql2k@topica.com</FONT> <BR><FONT face=Arial size=2>Subject:
[dba-SQLServer]Rolling back a transaction log?</FONT> </P><BR>
<P><FONT face=Arial size=2>Well it finally happened... We have a Complaint
database that runs in</FONT> <BR><FONT face=Arial size=2>conjunction with a
goldmine database. The Goldmine database tracks</FONT> <BR><FONT
face=Arial size=2>outgoing calls made by our company to customers in order to
track sales</FONT> <BR><FONT face=Arial size=2>leads and now complaints.
Since 4/1/2002 we have not had a database</FONT> <BR><FONT face=Arial
size=2>error or hiccup until now. The company's official DBA, while
working on</FONT> <BR><FONT face=Arial size=2>a development database on the
production server (yes, that's right)</FONT> <BR><FONT face=Arial
size=2>inadvertently wiped out my database instead of his test one this</FONT>
<BR><FONT face=Arial size=2>morning, The users of the Complaint db suddenly
began to complaining</FONT> <BR><FONT face=Arial size=2>that there were no
records, and upon checking I found this to be the</FONT> <BR><FONT face=Arial
size=2>case. I backup every night, and I have the log file backup when
it</FONT> <BR><FONT face=Arial size=2>reaches 60%. BUT. I did not
have *ANY* protection for the moments</FONT> <BR><FONT face=Arial
size=2>before the wipe out. Initially I panicked about not being able to
kick</FONT> <BR><FONT face=Arial size=2>the users out quickly enough... I
didn't bother to *remember* that I had</FONT> <BR><FONT face=Arial size=2>a
Kill All Users In Db script. So about 10 minutes later (after
kicking</FONT> <BR><FONT face=Arial size=2>all the users out) I restored the
database back to last log backup, but</FONT> <BR><FONT face=Arial size=2>that
was not good as it had the transactions that wiped out the</FONT> <BR><FONT
face=Arial size=2>database. SO I had to restore to last nights copy
officially killing</FONT> <BR><FONT face=Arial size=2>all entries from 10am
and prior. :(</FONT> </P>
<P><FONT face=Arial size=2>I've secured my script for killing Active Users in
the DB. And My boss</FONT> <BR><FONT face=Arial size=2>knows *who* wiped
out the database, in fact I made sure he knew as soon</FONT> <BR><FONT
face=Arial size=2>as it happened ... maybe that's not a good political move,
but I'm in</FONT> <BR><FONT face=Arial size=2>charge of the db. Now the
question is... Since the log file is only</FONT> <BR><FONT face=Arial
size=2>1meg long and on average it doesn't backup the log for perhaps every
2</FONT> <BR><FONT face=Arial size=2>to 3 days... (I do make a full backup
every night). I suppose I could</FONT> <BR><FONT face=Arial
size=2>manage the backups to include incremental changes every hr, so that
as</FONT> <BR><FONT face=Arial size=2>little data is lost? What do you
guys suggest?</FONT> </P><BR>
<P><FONT face=Arial size=2>-Francisco</FONT> <BR><A
href="http://rcm.netfirms.com"><U><FONT face=Arial color=#0000ff
size=2>http://rcm.netfirms.com</FONT></U></A> </P></BLOCKQUOTE>
<DIV>
<HR>
</DIV>
<DIV>Is email taking over your day? Manage your time with eMailBoss. Try it
free! <A
href="http://www.eMailBoss.com">http://www.eMailBoss.com</A></DIV></BODY></HTML>