Dear all,
if i delete a row in a table accidently , how can i recover that rows
in case 1: just delete it a few second before
in case 2: delete it and add several rows after deleted rowsSQL Server doesn't provided a 'undelete' method. You will have to read the
transaction log using commands like DBCC LOG, or fn_dblog. But there isn't
much official documentation available about these commands. Another
alternative is to use a tool like Lumigent Log Explorer that can recover
data from the transaction log, as long as that transaction is in the
transaction log.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CF2DC4B2-3CB8-47BA-B200-E91D03890E07@.microsoft.com...
Dear all,
if i delete a row in a table accidently , how can i recover that rows
in case 1: just delete it a few second before
in case 2: delete it and add several rows after deleted rows|||> Dear all,
> if i delete a row in a table accidently , how can i recover that rows
> in case 1: just delete it a few second before
> in case 2: delete it and add several rows after deleted rows
Backup is your friend here.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe
Vyas has already answered your question.
I'd like to add if do soem testing (delete,insert,update) on QA add BEGIN
TRAN before a statement and make sure that you did right thing and then
COMMIT
BEGIN TRAN
delete from Table
10000000 (rows) affected
Mistake!!!!!!!!!!!!!!!!!!!
Rollback Tran
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CF2DC4B2-3CB8-47BA-B200-E91D03890E07@.microsoft.com...
> Dear all,
> if i delete a row in a table accidently , how can i recover that rows
> in case 1: just delete it a few second before
> in case 2: delete it and add several rows after deleted rows|||Recovery is not a method if there have some rows added after the row is
deleted!|||If you had a backup, then you could at least restore it as a different
database, and selectively copy the required rows back into your database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:2D3C1228-60E5-4326-A55E-DF964EA474BD@.microsoft.com...
Recovery is not a method if there have some rows added after the row is
deleted!|||Hi Joe
>if i delete a row in a table accidently , how can i recover that rows
Accidents can ONLY be prevented with precautions.
You can write a trigger to avoid deletion using @.@.rowcount, if you dont have
regular deletions. You can use this to specific user also.
If you find that official regular deletions are inevitable(practically true)
use triggers to add to to history tables so that you can easily retrieve the
row you want.
Regards
R.D
"Narayana Vyas Kondreddi" wrote:
> If you had a backup, then you could at least restore it as a different
> database, and selectively copy the required rows back into your database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:2D3C1228-60E5-4326-A55E-DF964EA474BD@.microsoft.com...
> Recovery is not a method if there have some rows added after the row is
> deleted!
>
>
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Monday, March 26, 2012
Monday, February 20, 2012
record locking-is this all automatic?
Hi I am using SQL 2000 and will be working on a database that will have a lo
t
of users. I was wondering there may be a case were I need to lock a record,
like when one user will be modifying its' data. I was wondering how to do
this? I am using stored procedures. I will probably need to lock the recor
d
after a user does a select on a particular table. Also what are some of the
ways around this, would one be creating multiple duplicate tables so several
users can be modifying in a sense the same record at the same time?
Thanks.
--
Paul G
Software engineer.The common way is to create a Locks table. Then when you want to lock, for
example, a sales order, you would first check for a lock already there.
SELECT * FROM Locks
WHERE LockIdentifier = "SalesOrder4389"
and if you find a lock, then someone already is editing it. If you don't
find it, you can insert your own lock
INSERT INTO Locks (LockDate, LockedByUsername, LockedByFullname,
LockIdentifier, LockedAtMachineName)
VALUES (getdate(), 'paulg', 'Paul Ganonymous', 'SalesOrder4389', 'NERZHUL')
You lock other things by having different lock identifiers
e.g. "Invoice332", "Customer33", "Ticket 98", etc
The only downside is that if someone's PC crashes (or lose connection)
before they have a chance to clean up their lock, the lock will be dangling,
and you'll have to have an administrative screen to see locks and to remove
them. Also, whenever someone logs in, maybe clean up their locks then.
Alternativly, you can use the sp_getapplock/sp_releaseapplock built-in to
SQL Server. It works exactly like i described above, but it's built into SQL
Server, and SQL Sever will clean up any locks when a user disconnects (no
dangling locks)
To get a lock:
EXECUTE sp_getapplock 'SalesOrder4389', 'exclusive', 'Session', 0
To release a lock
EXECUTE sp_releaseapplock 'SalesOrder4389', 'Session'
With applocks, you could even go overboard and have
"Shared" lock if a user is only going to be looking at it (and others
can look too)
"Update" lock if a user is going to be changing something (others can
look, but nobody else can modify)
"Exclusive" lock means only i get it.
Important to note: "Application Locks" in SQL Server doesn't mean SQL Server
locks anything for you. You still have to check for locks by trying to get
an applock. If you don't get it, you have to write the code to not proceed
to viewing/editing.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.|||SQL Server books online covers transactions and locking.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.
t
of users. I was wondering there may be a case were I need to lock a record,
like when one user will be modifying its' data. I was wondering how to do
this? I am using stored procedures. I will probably need to lock the recor
d
after a user does a select on a particular table. Also what are some of the
ways around this, would one be creating multiple duplicate tables so several
users can be modifying in a sense the same record at the same time?
Thanks.
--
Paul G
Software engineer.The common way is to create a Locks table. Then when you want to lock, for
example, a sales order, you would first check for a lock already there.
SELECT * FROM Locks
WHERE LockIdentifier = "SalesOrder4389"
and if you find a lock, then someone already is editing it. If you don't
find it, you can insert your own lock
INSERT INTO Locks (LockDate, LockedByUsername, LockedByFullname,
LockIdentifier, LockedAtMachineName)
VALUES (getdate(), 'paulg', 'Paul Ganonymous', 'SalesOrder4389', 'NERZHUL')
You lock other things by having different lock identifiers
e.g. "Invoice332", "Customer33", "Ticket 98", etc
The only downside is that if someone's PC crashes (or lose connection)
before they have a chance to clean up their lock, the lock will be dangling,
and you'll have to have an administrative screen to see locks and to remove
them. Also, whenever someone logs in, maybe clean up their locks then.
Alternativly, you can use the sp_getapplock/sp_releaseapplock built-in to
SQL Server. It works exactly like i described above, but it's built into SQL
Server, and SQL Sever will clean up any locks when a user disconnects (no
dangling locks)
To get a lock:
EXECUTE sp_getapplock 'SalesOrder4389', 'exclusive', 'Session', 0
To release a lock
EXECUTE sp_releaseapplock 'SalesOrder4389', 'Session'
With applocks, you could even go overboard and have
"Shared" lock if a user is only going to be looking at it (and others
can look too)
"Update" lock if a user is going to be changing something (others can
look, but nobody else can modify)
"Exclusive" lock means only i get it.
Important to note: "Application Locks" in SQL Server doesn't mean SQL Server
locks anything for you. You still have to check for locks by trying to get
an applock. If you don't get it, you have to write the code to not proceed
to viewing/editing.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.|||SQL Server books online covers transactions and locking.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.
record locking-is this all automatic?
Hi I am using SQL 2000 and will be working on a database that will have a lot
of users. I was wondering there may be a case were I need to lock a record,
like when one user will be modifying its' data. I was wondering how to do
this? I am using stored procedures. I will probably need to lock the record
after a user does a select on a particular table. Also what are some of the
ways around this, would one be creating multiple duplicate tables so several
users can be modifying in a sense the same record at the same time?
Thanks.
--
Paul G
Software engineer.The common way is to create a Locks table. Then when you want to lock, for
example, a sales order, you would first check for a lock already there.
SELECT * FROM Locks
WHERE LockIdentifier = "SalesOrder4389"
and if you find a lock, then someone already is editing it. If you don't
find it, you can insert your own lock
INSERT INTO Locks (LockDate, LockedByUsername, LockedByFullname,
LockIdentifier, LockedAtMachineName)
VALUES (getdate(), 'paulg', 'Paul Ganonymous', 'SalesOrder4389', 'NERZHUL')
You lock other things by having different lock identifiers
e.g. "Invoice332", "Customer33", "Ticket 98", etc
The only downside is that if someone's PC crashes (or lose connection)
before they have a chance to clean up their lock, the lock will be dangling,
and you'll have to have an administrative screen to see locks and to remove
them. Also, whenever someone logs in, maybe clean up their locks then.
Alternativly, you can use the sp_getapplock/sp_releaseapplock built-in to
SQL Server. It works exactly like i described above, but it's built into SQL
Server, and SQL Sever will clean up any locks when a user disconnects (no
dangling locks)
To get a lock:
EXECUTE sp_getapplock 'SalesOrder4389', 'exclusive', 'Session', 0
To release a lock
EXECUTE sp_releaseapplock 'SalesOrder4389', 'Session'
With applocks, you could even go overboard and have
"Shared" lock if a user is only going to be looking at it (and others
can look too)
"Update" lock if a user is going to be changing something (others can
look, but nobody else can modify)
"Exclusive" lock means only i get it.
Important to note: "Application Locks" in SQL Server doesn't mean SQL Server
locks anything for you. You still have to check for locks by trying to get
an applock. If you don't get it, you have to write the code to not proceed
to viewing/editing.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.|||SQL Server books online covers transactions and locking.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.
of users. I was wondering there may be a case were I need to lock a record,
like when one user will be modifying its' data. I was wondering how to do
this? I am using stored procedures. I will probably need to lock the record
after a user does a select on a particular table. Also what are some of the
ways around this, would one be creating multiple duplicate tables so several
users can be modifying in a sense the same record at the same time?
Thanks.
--
Paul G
Software engineer.The common way is to create a Locks table. Then when you want to lock, for
example, a sales order, you would first check for a lock already there.
SELECT * FROM Locks
WHERE LockIdentifier = "SalesOrder4389"
and if you find a lock, then someone already is editing it. If you don't
find it, you can insert your own lock
INSERT INTO Locks (LockDate, LockedByUsername, LockedByFullname,
LockIdentifier, LockedAtMachineName)
VALUES (getdate(), 'paulg', 'Paul Ganonymous', 'SalesOrder4389', 'NERZHUL')
You lock other things by having different lock identifiers
e.g. "Invoice332", "Customer33", "Ticket 98", etc
The only downside is that if someone's PC crashes (or lose connection)
before they have a chance to clean up their lock, the lock will be dangling,
and you'll have to have an administrative screen to see locks and to remove
them. Also, whenever someone logs in, maybe clean up their locks then.
Alternativly, you can use the sp_getapplock/sp_releaseapplock built-in to
SQL Server. It works exactly like i described above, but it's built into SQL
Server, and SQL Sever will clean up any locks when a user disconnects (no
dangling locks)
To get a lock:
EXECUTE sp_getapplock 'SalesOrder4389', 'exclusive', 'Session', 0
To release a lock
EXECUTE sp_releaseapplock 'SalesOrder4389', 'Session'
With applocks, you could even go overboard and have
"Shared" lock if a user is only going to be looking at it (and others
can look too)
"Update" lock if a user is going to be changing something (others can
look, but nobody else can modify)
"Exclusive" lock means only i get it.
Important to note: "Application Locks" in SQL Server doesn't mean SQL Server
locks anything for you. You still have to check for locks by trying to get
an applock. If you don't get it, you have to write the code to not proceed
to viewing/editing.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.|||SQL Server books online covers transactions and locking.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:1861A5DD-7AE7-44AA-82A6-49FA918CA31A@.microsoft.com...
> Hi I am using SQL 2000 and will be working on a database that will have a
> lot
> of users. I was wondering there may be a case were I need to lock a
> record,
> like when one user will be modifying its' data. I was wondering how to do
> this? I am using stored procedures. I will probably need to lock the
> record
> after a user does a select on a particular table. Also what are some of
> the
> ways around this, would one be creating multiple duplicate tables so
> several
> users can be modifying in a sense the same record at the same time?
> Thanks.
> --
> Paul G
> Software engineer.
Record locking...
Hi Experts,
Here's the scenario of the case...
User1 does a retrieve of information, with the intention of updating it
later. At the same time, User2 does a retrieve of information, with the
intention of updating it later. User1 updates some of the row on the web
server, and then calls an update stored procedure.Then user2 updates some of
the row on the web server, and then calls an update stored procedure.
Some of user1s updates are lost because user2 had "stale" data.
So what a lot of applications do is to implement explicit locking, so that
when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
update it later". The rule is in that case that a locked record cannot also
be locked by someone else
and update can only be called if the record was locked by that user.
We can of course add a column to every table that has to support this
"locking", but I was wondering if SQL Serversystems support some kind of
"lock this record please"?Here's an old archive on this type of issue:
http://tinyurl.com/5qevd
-oj
"Lia" <Lia@.discussions.microsoft.com> wrote in message
news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
> Hi Experts,
> Here's the scenario of the case...
> User1 does a retrieve of information, with the intention of updating it
> later. At the same time, User2 does a retrieve of information, with the
> intention of updating it later. User1 updates some of the row on the web
> server, and then calls an update stored procedure.Then user2 updates some
> of
> the row on the web server, and then calls an update stored procedure.
> Some of user1s updates are lost because user2 had "stale" data.
> So what a lot of applications do is to implement explicit locking, so that
> when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
> update it later". The rule is in that case that a locked record cannot
> also
> be locked by someone else
> and update can only be called if the record was locked by that user.
> We can of course add a column to every table that has to support this
> "locking", but I was wondering if SQL Serversystems support some kind of
> "lock this record please"?|||Hi Experts,
What is usually the system? Is it the person who updates gets the priority
or the person who retrieves (for update) gets a priority.
I have been debating on this for some time and after reading a few books i
got the impression that LOCKING records was the practice in the olden days
and in the current situation ( adLockOptimistic ) the person who clicks
update first get a priority and the other person gets an ERROR! Which can be
trapped using ON Error GOTO statement
Please clarify and help me resolve this issue.
Thanks
Manish
"oj" wrote:
> Here's an old archive on this type of issue:
> http://tinyurl.com/5qevd
>
> --
> -oj
>
> "Lia" <Lia@.discussions.microsoft.com> wrote in message
> news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
>
>|||Hi Manish,
Thanks for your reply on my post. :-) The system will supposed to give the
priority to person who retrieves(for update) in such a way that upon
retrieving, the record will be locked. The lock will then be released after
updating the record or canceling the transaction. And if the other person
select the same record that intend to update it also, message will be given
to inform the person that the record can only be viewed and cannot be update
d
because it is locked for update by another user.
Your suggestions and comments will be highly appreciated.
Thanks,
Lia
"Manish Sawjiani" wrote:
> Hi Experts,
> What is usually the system? Is it the person who updates gets the priority
> or the person who retrieves (for update) gets a priority.
> I have been debating on this for some time and after reading a few books i
> got the impression that LOCKING records was the practice in the olden days
> and in the current situation ( adLockOptimistic ) the person who clicks
> update first get a priority and the other person gets an ERROR! Which can
be
> trapped using ON Error GOTO statement
> Please clarify and help me resolve this issue.
> Thanks
> Manish
> "oj" wrote:
>
Here's the scenario of the case...
User1 does a retrieve of information, with the intention of updating it
later. At the same time, User2 does a retrieve of information, with the
intention of updating it later. User1 updates some of the row on the web
server, and then calls an update stored procedure.Then user2 updates some of
the row on the web server, and then calls an update stored procedure.
Some of user1s updates are lost because user2 had "stale" data.
So what a lot of applications do is to implement explicit locking, so that
when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
update it later". The rule is in that case that a locked record cannot also
be locked by someone else
and update can only be called if the record was locked by that user.
We can of course add a column to every table that has to support this
"locking", but I was wondering if SQL Serversystems support some kind of
"lock this record please"?Here's an old archive on this type of issue:
http://tinyurl.com/5qevd
-oj
"Lia" <Lia@.discussions.microsoft.com> wrote in message
news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
> Hi Experts,
> Here's the scenario of the case...
> User1 does a retrieve of information, with the intention of updating it
> later. At the same time, User2 does a retrieve of information, with the
> intention of updating it later. User1 updates some of the row on the web
> server, and then calls an update stored procedure.Then user2 updates some
> of
> the row on the web server, and then calls an update stored procedure.
> Some of user1s updates are lost because user2 had "stale" data.
> So what a lot of applications do is to implement explicit locking, so that
> when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
> update it later". The rule is in that case that a locked record cannot
> also
> be locked by someone else
> and update can only be called if the record was locked by that user.
> We can of course add a column to every table that has to support this
> "locking", but I was wondering if SQL Serversystems support some kind of
> "lock this record please"?|||Hi Experts,
What is usually the system? Is it the person who updates gets the priority
or the person who retrieves (for update) gets a priority.
I have been debating on this for some time and after reading a few books i
got the impression that LOCKING records was the practice in the olden days
and in the current situation ( adLockOptimistic ) the person who clicks
update first get a priority and the other person gets an ERROR! Which can be
trapped using ON Error GOTO statement
Please clarify and help me resolve this issue.
Thanks
Manish
"oj" wrote:
> Here's an old archive on this type of issue:
> http://tinyurl.com/5qevd
>
> --
> -oj
>
> "Lia" <Lia@.discussions.microsoft.com> wrote in message
> news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
>
>|||Hi Manish,
Thanks for your reply on my post. :-) The system will supposed to give the
priority to person who retrieves(for update) in such a way that upon
retrieving, the record will be locked. The lock will then be released after
updating the record or canceling the transaction. And if the other person
select the same record that intend to update it also, message will be given
to inform the person that the record can only be viewed and cannot be update
d
because it is locked for update by another user.
Your suggestions and comments will be highly appreciated.
Thanks,
Lia
"Manish Sawjiani" wrote:
> Hi Experts,
> What is usually the system? Is it the person who updates gets the priority
> or the person who retrieves (for update) gets a priority.
> I have been debating on this for some time and after reading a few books i
> got the impression that LOCKING records was the practice in the olden days
> and in the current situation ( adLockOptimistic ) the person who clicks
> update first get a priority and the other person gets an ERROR! Which can
be
> trapped using ON Error GOTO statement
> Please clarify and help me resolve this issue.
> Thanks
> Manish
> "oj" wrote:
>
Subscribe to:
Posts (Atom)