no "UNDO" feature in SQL? So what do people do to undo mistakes?

Given that there are is no "UNDO" feature in SQL, says my textbook,
what are the best practices in SQL to avoid making a irreversible
mistake? For example, backup your database prior to modifying it with
an UPDATE command, or, make a copy of a table with SELECT * prior to
modifying a table?
RL"raylopez99" <raylopez99@.yahoo.com> wrote in message
news:1185342099.040833.177280@.d55g2000hsg.googlegroups.com...
> Given that there are is no "UNDO" feature in SQL, says my textbook,
> what are the best practices in SQL to avoid making a irreversible
> mistake? For example, backup your database prior to modifying it with
> an UPDATE command, or, make a copy of a table with SELECT * prior to
> modifying a table?
> RL
>
The answer depends on the context. If you are performing some one-off
maintenance task then do it in a transaction and verify the results
immediately. Only commit if the test is good. Of course you would always
have a backup anyway wouldn't you?
In an application context you might decide to preserve a history of changes
or just rely on FULL RECOVERY and point-in-time restore.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 24, 11:10 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> The answer depends on the context. If you are performing some one-off
> maintenance task then do it in a transaction and verify the results
> immediately. Only commit if the test is good. Of course you would always
> have a backup anyway wouldn't you?
> In an application context you might decide to preserve a history of changes
> or just rely on FULL RECOVERY and point-in-time restore.
>
Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
I expect MS Visual Studio and MS SQL Server 2005 to support it.
In any event, SQL the Dinosaur Macro Language needs an "undo" feature
IMO (as well as a feature telling you how exactly your query was
performed--for example Indexes are not even implemented up front but
'behind the scenes', but that's a rant for another day.
RL
You must not confuse the recovery model with functionailty and what it
will allow
you to. The full recovery model is exactly what it says it is. It's to
do with
your disaster recovery planning. If you run your database in full
recovery mode
then you will log as much data as SQL will let you to the transaction
logs.
However, you should only do this when you are maintaining an unbroken
sequence of
transaction log backups. There would be no point in logging everything
to the
transaction log is anything else could truncate that log file.
Restarting SQL will
therefore not truncate your logs. BOL does explain this pretty well I
believe,
although I think it remains ambiguous as to whether the full backup
would truncate
it. It does not. Only a Log Backup will clear down the inactive
portion of the log
in Full mode. Full recovery does not restrict the commands you can
run, it's
simply for disaster planning.
Cheers
Nick Hall Senior Database Administrator|||> Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> I expect MS Visual Studio and MS SQL Server 2005 to support it.
Yes, they do. Read up on BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and RESTORE LOG.
> In any event, SQL the Dinosaur Macro Language needs an "undo" feature
There already is. BEGIN, COMMIT and ROLLBACK TRANSACTION. There are also various SET settings to
influence behavior, like SET IMPLICIT TRANSACTION and SET XACT_ABORT.
> (as well as a feature telling you how exactly your query was
> performed--for example Indexes are not even implemented up front but
> 'behind the scenes', but that's a rant for another day.
I'm not sure what you mean by the "behind the scenes" comment, but there are many features and
option to investigate the execution plan and query cost.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"raylopez99" <raylopez99@.yahoo.com> wrote in message
news:1185346012.395305.5730@.q75g2000hsh.googlegroups.com...
> On Jul 24, 11:10 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
>> The answer depends on the context. If you are performing some one-off
>> maintenance task then do it in a transaction and verify the results
>> immediately. Only commit if the test is good. Of course you would always
>> have a backup anyway wouldn't you?
>> In an application context you might decide to preserve a history of changes
>> or just rely on FULL RECOVERY and point-in-time restore.
> Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> I expect MS Visual Studio and MS SQL Server 2005 to support it.
> In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> IMO (as well as a feature telling you how exactly your query was
> performed--for example Indexes are not even implemented up front but
> 'behind the scenes', but that's a rant for another day.
>
> RL
>
> You must not confuse the recovery model with functionailty and what it
> will allow
> you to. The full recovery model is exactly what it says it is. It's to
> do with
> your disaster recovery planning. If you run your database in full
> recovery mode
> then you will log as much data as SQL will let you to the transaction
> logs.
> However, you should only do this when you are maintaining an unbroken
> sequence of
> transaction log backups. There would be no point in logging everything
> to the
> transaction log is anything else could truncate that log file.
> Restarting SQL will
> therefore not truncate your logs. BOL does explain this pretty well I
> believe,
> although I think it remains ambiguous as to whether the full backup
> would truncate
> it. It does not. Only a Log Backup will clear down the inactive
> portion of the log
> in Full mode. Full recovery does not restrict the commands you can
> run, it's
> simply for disaster planning.
> Cheers
> Nick Hall Senior Database Administrator
>|||On Jul 25, 6:30 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> > I expect MS Visual Studio and MS SQL Server 2005 to support it.
> Yes, they do. Read up on BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and RESTORE LOG.
> > In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> There already is. BEGIN, COMMIT and ROLLBACK TRANSACTION. There are also various SET settings to
> influence behavior, like SET IMPLICIT TRANSACTION and SET XACT_ABORT.
> > (as well as a feature telling you how exactly your query was
> > performed--for example Indexes are not even implemented up front but
> > 'behind the scenes', but that's a rant for another day.
> I'm not sure what you mean by the "behind the scenes" comment, but there are many features and
> option to investigate the execution plan and query cost.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "raylopez99" <raylope...@.yahoo.com> wrote in message
> news:1185346012.395305.5730@.q75g2000hsh.googlegroups.com...
> > On Jul 24, 11:10 pm, "David Portas"
> > <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> >> The answer depends on the context. If you are performing some one-off
> >> maintenance task then do it in a transaction and verify the results
> >> immediately. Only commit if the test is good. Of course you would always
> >> have a backup anyway wouldn't you?
> >> In an application context you might decide to preserve a history of changes
> >> or just rely on FULL RECOVERY and point-in-time restore.
> > Thanks. Never heard of FULL RECOVERY and I'll do more research on it;
> > I expect MS Visual Studio and MS SQL Server 2005 to support it.
> > In any event, SQL the Dinosaur Macro Language needs an "undo" feature
> > IMO (as well as a feature telling you how exactly your query was
> > performed--for example Indexes are not even implemented up front but
> > 'behind the scenes', but that's a rant for another day.
> > RL
> > You must not confuse the recovery model with functionailty and what it
> > will allow
> > you to. The full recovery model is exactly what it says it is. It's to
> > do with
> > your disaster recovery planning. If you run your database in full
> > recovery mode
> > then you will log as much data as SQL will let you to the transaction
> > logs.
> > However, you should only do this when you are maintaining an unbroken
> > sequence of
> > transaction log backups. There would be no point in logging everything
> > to the
> > transaction log is anything else could truncate that log file.
> > Restarting SQL will
> > therefore not truncate your logs. BOL does explain this pretty well I
> > believe,
> > although I think it remains ambiguous as to whether the full backup
> > would truncate
> > it. It does not. Only a Log Backup will clear down the inactive
> > portion of the log
> > in Full mode. Full recovery does not restrict the commands you can
> > run, it's
> > simply for disaster planning.
> > Cheers
> > Nick Hall Senior Database Administrator
I'm glad someone mentioned this. Yes, as good as SQL is at doing it's
own transactions you CAN tell it when to BEGIN, COMMIT, and ROLLLBACK
transactions. Rolling back a trasaction will take you to the point
before the begin tansaction was started which, in effect, is the
"undo" you're asking about.