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.

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
--

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
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.googlegrou ps.com...
> On Jul 24, 11:10 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> 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.
> 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:[vbcol=seagreen]
> Yes, they do. Read up on BACKUP DATABASE, BACKUP LOG, RESTORE DATABASE and RESTORE LOG.
>
> 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.
>
> 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.googlegrou ps.com...
>
>
>
>
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.

NH: Best Practices Approach - call stored proc - or run it via linked server?

what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.

i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?

thanks.

Jeff

anyone?|||

One obvious thing to keep in mind is the performance. Depending on your network the executiion time could be different.

Now I am not sure if I understand your question. If you do not have permission to create the stored proc on the original server you would still need access to the underlying tables if you want to access them through server B, irrespective of whether its via stored proc or directly T-SQL.

|||

well typically I associate using a linked server to pull data from something using openquery such as ibm as400 so my question is do i get access to put the stored proc on the remote sql server (not one of my groups sql servers), or do i ask for setting up a linked server to it and execute the stored proc from my local sql server using the linked server approach.

either way?
get access to put the stored proc right on the sql server and call it in c#?
use the linked server and call it in c#

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

thanks,

JB

|||

info@.learnbartending.com:

Just looking for some reason why one way may be better than another, primarily performance, and reducing putting in another possible point of failure.

Those are exactly the reasons why you might want to put the proc directly where the data is.