[dba-SQLServer]Rolling back a transaction log?

Francisco H Tapia my.lists at verizon.net
Fri Feb 21 15:09:36 CST 2003


Thanks for the advise Charles.  This database for the most part had been
running well for almost a year before our DBA accidentally deleted
everything from the production db.  I did warn him against placing the
development copy of the db on the same server.. .he advised me that he would
be careful.  Other than running to my boss to cry fowl.  He has learned a
valuable lesson, I am glad that not too many hours of work had been lost,
(just 2).  but in those 2 hours, only a few records were affected... I have
updated the maintenance to backup up to the last hour.  In any case it looks
like I would have to go diving into the transaction log for the last
checkpoint and undoing the delete statements...

-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Wortz, Charles" <CWortz at tea.state.tx.us>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Friday, February 21, 2003 12:17 PM
Subject: RE: [dba-SQLServer]Rolling back a transaction log?


John,

This is not an area where I claim any expertise.  What I know is every
transaction written to the main db is also supposed to be written to its
mirror.  How they resync them after one goes down is not something I
have had to worry about.  There may be others on this list that can
speak to that.


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: John W. Colby [mailto:jcolby at colbyconsulting.com]
Sent: Friday 2003 Feb 21 14:07
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Rolling back a transaction log?


Charles,

>But the mirror gives a real-time backup which may solve half of
Francisco's problem.

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?).


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Wortz,
Charles
Sent: Friday, February 21, 2003 3:00 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer]Rolling back a transaction log?



John,

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.

Charles Wortz


-----Original Message-----
From:   dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com
<mailto:dba-sqlserver-admin at databaseadvisors.com> ]  On Behalf Of John
W. Colby

Sent:   Friday 2003 Feb 21 13:47
To:     dba-sqlserver at databaseadvisors.com
Subject:        RE: [dba-SQLServer]Rolling back a transaction
log?

Would mirroring really help if someone intentionally deletes it?
Wouldn't the mirror be deleted as well?

John W. Colby
Colby Consulting
www.ColbyConsulting.com <file://www.ColbyConsulting.com>

-----Original Message-----
From: dba-sqlserver-admin at databaseadvisors.com
[mailto:dba-sqlserver-admin at databaseadvisors.com]On
<mailto:dba-sqlserver-admin at databaseadvisors.com]On>  Behalf Of Wortz,
Charles
Sent: Friday, February 21, 2003 2:34 PM
To: dba-sqlserver at databaseadvisors.com; sswug-sql2k at topica.com
Subject: RE: [dba-SQLServer]Rolling back a transaction log?


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


-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net
<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 <http://rcm.netfirms.com>






More information about the dba-SQLServer mailing list