Hi,
I am a beginner in SQL/RDBMS and learning the different nuances of the SQL
Server. I know that all activities in a database are stored in a transaction
log and I can see where the file is stored. My question is how do I read any
information from a transaction log?
For example I delete a row manually in the Enterprise Manager (which I
should not have deleted), how do I Rollback that delete or how do I read the
transaction log to see what has happened and reverse that action.
Any help will be highly appreciated.
Thanks in advance
Aditya.There isn't a documented method to read transaction log data. You can get
some info by using undocumented commands like DBCC LOG. A better method is
with a third party tool like Lumigent's Log Explorer www.lumigent.com.
> For example I delete a row manually in the Enterprise Manager (which I
> should not have deleted), how do I Rollback that delete or how do I read
the
> transaction log to see what has happened and reverse that action.
If you are running in the FULL or BULK_LOGGED recovery model, you can backup
the current transaction log, restore the database (to a different name if
you want to keep the existing database intact) and then apply your
transaction log backups up to the point of the delete. See the Books Online
for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aditya" <grafica@.md2.vsnl.net.in> wrote in message
news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I am a beginner in SQL/RDBMS and learning the different nuances of the SQL
> Server. I know that all activities in a database are stored in a
transaction
> log and I can see where the file is stored. My question is how do I read
any
> information from a transaction log?
> For example I delete a row manually in the Enterprise Manager (which I
> should not have deleted), how do I Rollback that delete or how do I read
the
> transaction log to see what has happened and reverse that action.
> Any help will be highly appreciated.
> Thanks in advance
> Aditya.
>|||Hi Aditya
Dan answered your question about reading the log.
For rollback, there is no way to do it from Enterprise Manager. Each insert,
update and delete is auto-committed.
The only alternative is to use a query tool like query analyzer, and use a
BEGIN TRANSACTION before any changes, and then if you're satisified with the
change you can COMMIT TRANSACTION, if you're not, you can ROLLBACK
TRANSACTION.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aditya" <grafica@.md2.vsnl.net.in> wrote in message
news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I am a beginner in SQL/RDBMS and learning the different nuances of the SQL
> Server. I know that all activities in a database are stored in a
transaction
> log and I can see where the file is stored. My question is how do I read
any
> information from a transaction log?
> For example I delete a row manually in the Enterprise Manager (which I
> should not have deleted), how do I Rollback that delete or how do I read
the
> transaction log to see what has happened and reverse that action.
> Any help will be highly appreciated.
> Thanks in advance
> Aditya.
>|||Just to comment on both Dan and Kalen's posts, Log Explorer will allow you
to generate SQL commands to redo or undo a specific transaction. That comes
in handy sometimes when developers have direct access to production
databases. :)
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Aditya" <grafica@.md2.vsnl.net.in> wrote in message
news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I am a beginner in SQL/RDBMS and learning the different nuances of the SQL
> Server. I know that all activities in a database are stored in a
transaction
> log and I can see where the file is stored. My question is how do I read
any
> information from a transaction log?
> For example I delete a row manually in the Enterprise Manager (which I
> should not have deleted), how do I Rollback that delete or how do I read
the
> transaction log to see what has happened and reverse that action.
> Any help will be highly appreciated.
> Thanks in advance
> Aditya.
>|||Hi,
Thank you all for your posts. I will try them and get back if I have any
other queries.
Thanks again.
Aditya.
"Aditya" <grafica@.md2.vsnl.net.in> wrote in message
news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> Hi,
> I am a beginner in SQL/RDBMS and learning the different nuances of the SQL
> Server. I know that all activities in a database are stored in a
transaction
> log and I can see where the file is stored. My question is how do I read
any
> information from a transaction log?
> For example I delete a row manually in the Enterprise Manager (which I
> should not have deleted), how do I Rollback that delete or how do I read
the
> transaction log to see what has happened and reverse that action.
> Any help will be highly appreciated.
> Thanks in advance
> Aditya.
>|||There is also LogPi: http://www.logpi.com/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Aditya" <grafica@.md2.vsnl.net.in> wrote in message
news:%23KzpPsz4DHA.2696@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Thank you all for your posts. I will try them and get back if I have any
> other queries.
> Thanks again.
> Aditya.
> "Aditya" <grafica@.md2.vsnl.net.in> wrote in message
> news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I am a beginner in SQL/RDBMS and learning the different nuances of the
SQL
> > Server. I know that all activities in a database are stored in a
> transaction
> > log and I can see where the file is stored. My question is how do I read
> any
> > information from a transaction log?
> >
> > For example I delete a row manually in the Enterprise Manager (which I
> > should not have deleted), how do I Rollback that delete or how do I read
> the
> > transaction log to see what has happened and reverse that action.
> >
> > Any help will be highly appreciated.
> >
> > Thanks in advance
> > Aditya.
> >
> >
>|||Hi,
Still I like to known the internal format from the transactionlog. I can't
find it on the internet.
Greetz.
"Mike Epprecht [SQL Server MVP]" <mike@.NOSPAMepprecht.net> wrote in message
news:uVhuhO64DHA.2496@.TK2MSFTNGP09.phx.gbl...
> There is also LogPi: http://www.logpi.com/
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Epprecht Consulting (PTY) LTD
> Johannesburg, South Africa
> Mobile: +27-82-552-0268
> IM: mike@.NOSPAMepprecht.net
> Specialist SQL Server Solutions and Consulting
> "Aditya" <grafica@.md2.vsnl.net.in> wrote in message
> news:%23KzpPsz4DHA.2696@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Thank you all for your posts. I will try them and get back if I have any
> > other queries.
> >
> > Thanks again.
> > Aditya.
> >
> > "Aditya" <grafica@.md2.vsnl.net.in> wrote in message
> > news:O2CkJSq4DHA.2888@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > I am a beginner in SQL/RDBMS and learning the different nuances of the
> SQL
> > > Server. I know that all activities in a database are stored in a
> > transaction
> > > log and I can see where the file is stored. My question is how do I
read
> > any
> > > information from a transaction log?
> > >
> > > For example I delete a row manually in the Enterprise Manager (which I
> > > should not have deleted), how do I Rollback that delete or how do I
read
> > the
> > > transaction log to see what has happened and reverse that action.
> > >
> > > Any help will be highly appreciated.
> > >
> > > Thanks in advance
> > > Aditya.
> > >
> > >
> >
> >
>|||It is not published. Possibly, if you sign such an agreement that these
vendors has signed with MS, you can get your hands on it. Assuming that such
an agreement does exists for the structure of the t-log, of course. These
vendors might have reverse-engineered the log structure the hard way.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Renegade" <Nospam-123-123@.hotmail.com> wrote in message
news:40180e6c$0$333$e4fe514c@.news.xs4all.nl...
> Hi,
> Still I like to known the internal format from the transactionlog. I can't
> find it on the internet.
> Greetz.
>
