SQL Server 2005

[suarve]

Hi,

I have just stupidly forgot to include a where caluse in an sql statement. Now my sql has updated the whole table to the same information, so Im fairly scared now.

Is there a way to reverse this update? At the minute, I'm thinking I may need to go to my webhost and ask for a backup to be restored.

Many thanks

 

[Pho]

You'll need to restore from a backup.

Pro tip: always run a backup before running an update / delete SQL command .

I did a similar thing a while ago, I almost lost a day's worth of very complex work by restoring the wrong database - fortunately I left some of the stored procedures I was writing open in management studio so it wasn't too painful!

 

[suarve]

Phew phew phew!

Just noticed I had a tab open of the table, as of about 30 mins ago. As its only a category table (27 records) I was able to manually copy/paste the dat back in.

In my panic, just read about rollback transaction
when using stored procedures, so I guess that a good thing to use in the future.

/relief!

 

[Pho]

Nice .

Rollbacks are useful if your stored procedure alters several tables or runs several commands and one of them fails - it can undo everything it just did without leaving everything in a mess .

Here's a template I'm using for them:

BEGIN TRY
	BEGIN TRANSACTION
	
	-- DO STUFF HERE
	
	-- Everything was OK, save to database		
	COMMIT TRAN
	RETURN 1
END TRY	
-- Something went wrong, rollback
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK TRAN
		
	DECLARE @ErrMsg nvarchar(max), @ErrSev INT, @ErrSt INT
		SELECT @ErrMsg = ERROR_MESSAGE()
		SELECT @ErrSev = ERROR_SEVERITY()
		SELECT @ErrSt = ERROR_STATE() 			
	
	RAISERROR(@ErrMsg, @ErrSev, @ErrSt)
	
	RETURN 0
END CATCH

 

[lokkers]

You'll need to restore from a backup.

Pro tip: always run a backup before running an update / delete SQL command .

I did a similar thing a while ago, I almost lost a day's worth of very complex work by restoring the wrong database - fortunately I left some of the stored procedures I was writing open in management studio so it wasn't too painful!

when writing updates/deletes, I write the where clause first - I am somewhat paranoid about accidentally executing it.

My old dba would always write a random swearword in the middle of the statement so it wouldn't execute accidentally