|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Rollback an ad-hoc query from QA?Just curious how could one rollback an ad-hoc query executed from Query
Analyzer? The analyst did not use any BEGIN TRANSACTIONS statements, just a simple Update statement with too broad criteria. Does this mean the query is committed as soon as it completes and we would have to restore the database from the previous night's backup and then restore the transaction log to a time just before the erroneous SQL query? The database is set to full Recover Model. You are correct in your assumptions. But before the first restore, do a backup of the transaction
log (else you will only be able to restore until your last transaction log backup). Or use a log reader tool. Some more info at: http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Mnemonic" <MICHAEL_SUN***@COUNTRYWIDE.COM> wrote in message news:1124463238.770683.153960@g44g2000cwa.googlegroups.com... > Just curious how could one rollback an ad-hoc query executed from Query > Analyzer? The analyst did not use any BEGIN TRANSACTIONS statements, > just a simple Update statement with too broad criteria. Does this mean > the query is committed as soon as it completes and we would have to > restore the database from the previous night's backup and then restore > the transaction log to a time just before the erroneous SQL query? > > > The database is set to full Recover Model. > That's about it. If they didn't include it in a transaction it was
committed when it finished. You can also use one of the third party tools that let you manipulate the logs. -- Show quoteAndrew J. Kelly SQL MVP "Mnemonic" <MICHAEL_SUN***@COUNTRYWIDE.COM> wrote in message news:1124463238.770683.153960@g44g2000cwa.googlegroups.com... > Just curious how could one rollback an ad-hoc query executed from Query > Analyzer? The analyst did not use any BEGIN TRANSACTIONS statements, > just a simple Update statement with too broad criteria. Does this mean > the query is committed as soon as it completes and we would have to > restore the database from the previous night's backup and then restore > the transaction log to a time just before the erroneous SQL query? > > > The database is set to full Recover Model. > On Fri, 19 Aug 2005 11:26:11 -0400, Andrew J. Kelly wrote:
>That's about it. If they didn't include it in a transaction it was Hi Andrew,>committed when it finished. Unless the analyst had the setting for IMPLICIT_TRANSACTION set to ON. (Does anyone actually ever use that set option?) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Hi Hugo,
> (Does anyone actually ever use that set option?) I recall tracing either RDO or ADO about VB4 release timeframe (there was a bug in the transaction handling so you couldn't do rollback). Either one or both of those API's used the setting (back then). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:brfcg11r29vosqhj1l2acgodg3rhs5r3b2@4ax.com... > On Fri, 19 Aug 2005 11:26:11 -0400, Andrew J. Kelly wrote: > >>That's about it. If they didn't include it in a transaction it was >>committed when it finished. > > Hi Andrew, > > Unless the analyst had the setting for IMPLICIT_TRANSACTION set to ON. > > (Does anyone actually ever use that set option?) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) Well true, but I figured if no one was screaming about being blocked by now
they didn't have that option set<g>. -- Show quoteAndrew J. Kelly SQL MVP "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:brfcg11r29vosqhj1l2acgodg3rhs5r3b2@4ax.com... > On Fri, 19 Aug 2005 11:26:11 -0400, Andrew J. Kelly wrote: > >>That's about it. If they didn't include it in a transaction it was >>committed when it finished. > > Hi Andrew, > > Unless the analyst had the setting for IMPLICIT_TRANSACTION set to ON. > > (Does anyone actually ever use that set option?) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)  |
|||||||||||||||||||||||
Other interesting topics