avoiding accidental deletes...best practises?

Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

also are there any 'best practises' to avoid accidental deletes.

For example Im making a lot of delete statements like:

del from table_name
where item_id = 23

but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

Let me ask this question: would you want a script stopping half-way through a stored procedure ?

To wait for a user dialog box to complete at 4am ?

If you are having problems with accidential deletes, consider investing in Lumigent log explorer (www.lumigent.com) or Red Gates Log Rescue (www.red-gate.com) - these tools will allow you to recover the data you deleted.

Regards,

DB007

|||

use it in a transaction ....

keep autocommit off....and keep ur logs (incase it still happens) to recover the data..

|||

Hi Airwalker,

The thing to remember is accidents will happen, and just to have processes in place to help clear up the mess afterwards.

Even with transactions (if used correctly) once committed the data is still deleted (accidentially, maliciously - by a hacker !).

The options available to you are : Recover directly from the transaction log (as per previous post), Restore a prior database backup and copy the data back into the live system, make the database read-only.

Now option 3 is unlikely as this system requires updates, but suitable for systems that are designed to be read-only.

Option 2 - takes a long time, even with page level restores available in SQL 2005 - and normally a significant amount of disk space (esp. if a copy of the database needs to be taken).

Option 1 - Usually involves the least amount of time, and can be performed offline whilst users are continuing to use the system.

Best Regards,

D

|||thanks for the advice guys...much appreciated.|||

If you are running SQL Server 2005, you might consider using SQL Server 2005 database snapshots. They are great for allowing you to very easily recover data that gets altered or deleted by mistakes like that.

You could have an Agent job that periodically creates a snapshot and gets rid of older snapshots. Then, you can pull data from the most recent snapshot to fix those kind of mistakes. Just be aware that DB snapshots are by no means a replacement for a good backup strategy.

http://glennberrysqlperformance.spaces.live.com

|||

one method i have adoped is .. from my application i can delete (for Eg) only one users roles (1-M) from UserROle table.. So i have created a Trigger which checks whether deleted table contains morethan one user if yes roll back and exist.. So from QA such delete statment can be prevented....

One more method is Just create a trigger in all table which checks Count(*) from deleted table and it is 0 then it means you are deleting all the rows... roll back and raiserror..

All these methods depends... it may or may not valid in all scenario...

try this script and do the necessary modificaiton

drop table test

create table test (col1 int)

insert into test select 1

insert into test select 2

insert into test select 3

insert into test select 4

insert into test select 5

insert into test select 6

insert into test select 1

insert into test select 1

alter trigger testtrig

on test for delete

as

declare @.i int

select @.i=count(*) from test

print @.i

if @.i=0

begin

print 'u r trying to delete blah...blah'

rollback tran

end

delete from test

delete from test where col1=1

select *From test

Madhu