Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rolling Back the update!!!! Help

Author  Topic 

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-02-05 : 08:52:43
Hi I have made a mistake,they asked me to update some culumns in a table but i did it the wrong way they did not want. How to change back to an old data that was in the culumn? I used the REPLACE STRING ,To find out it is not what they wanted they only wanted some string to be added on the connection source.!

Please help on how to do the UnUpdate to several culums i just updated!!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-05 : 08:56:52
Is the database in the full recovery model? If so, take a backup of the database and restore to the point before your erroneous update using the STOPAT keyword of the restore syntax

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 08:58:36
I doubt if developers will have sufficient access to do the point in time restore (at least in our place its restricted for DBAs alone)
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-05 : 08:59:24
Here is a script for a point-in-time restore
RESTORE DATABASE DBUtil 
FROM DISK = 'C:\Backup\dbutil.bak'
WITH NORECOVERY
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_1.trn'
WITH NORECOVERY

RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_2.trn'
WITH NORECOVERY

RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_3.trn'
WITH RECOVERY,
STOPAT = '2010-02-05 12:02:55'


- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

rajpes
Starting Member

13 Posts

Posted - 2010-02-05 : 09:03:16
WHAT IF HE HAD NOT TAKEN ANY BACK UP BEFORE UPDATING?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:07:10
Problem with a restore is that it will take the whole database back to that point ... not just that table. (Although I don't think you have any choice from what you have described - you could restore to a new, temporary, database and then reinstate the original data in your table by JOIN from your Main database to the Temporary one).

For next time here is a suggestion. Before adhoc updates I do:

SELECT *
INTO TEMP_MyTableName_20100205 -- I.e. yyyymmdd
FROM MyTable

and then I have a source of data I can either put back, or I can use for a selective update if I did it wrong (or the user says "I didn't mean that"!!!)

I always start the tablename with "TEMP_" so they sort alphabetically, and because the date is on the end of the temporary table after a few days / weeks / months I can purge the tables.

NOTE: For our LIVE databases we also have a TEST database on the same server, and I put the "TEMP" tables in the TEST database, so as not to extend the TLogs and cause potentially blocking of other users.

SELECT *
INTO MyTestDB.dbo.TEMP_MyTableName_20100205 -- I.e. yyyymmdd
FROM MyLiveDB.dbo.MyTable
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:07:53
"WHAT IF HE HAD NOT TAKEN ANY BACK UP BEFORE UPDATING?"

Doesn't matter. Take a (final) TLog backup before starting the restore (provided that the database is in Full Recovery model)
Go to Top of Page

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-02-05 : 09:07:59
The Database is in Simple mode. and the last back that was done was round about 08:43am today?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:09:59
quote:
Originally posted by Zoma

The Database is in Simple mode. and the last back that was done was round about 08:43am today?


has update happened after?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:12:29
Restore it to a New, Temporary, database and reinstate the data from that backup:

SELECT DEST.PrimaryKeyID, [Live]=DEST.MyColumn, [Restored]=SRC.MyColumn
-- UPDATE DEST SET MyColumn = SRC.MyColumn
FROM MyTable AS DEST
JOIN MyTempDatabase.dbo.MyTable AS SRC
ON SRC.PrimaryKeyID = DEST.PrimaryKeyID
WHERE DEST.MyColumn <> SRC.MyColumn
OR (DEST.MyColumn IS NULL AND SRC.MyColumn IS NOT NULL)
OR (DEST.MyColumn IS NOT NULL AND SRC.MyColumn IS NULL)

If the SELECT looks OK then comment-in the UPDATE
Go to Top of Page

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-02-05 : 09:13:03
Damn....yes it was...but it was just only one culumn that i changed and only one string name...It is a parameter table. Just the server name,user and password....damn..
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:15:35
If MyColumn is a VARCHAR, and there is a chance that there are capitalisation differences - and you have a Case INsensitive database then change to:

WHERE DEST.MyColumn COLLATE Latin1_General_BIN <> SRC.MyColumn
OR ...

or similar to force a case SENSITIVE comparison

Go to Top of Page

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-02-05 : 09:16:57
Ok now...How to check how many updates were done in a database like as in today coz im really not sure? isnt there a script to check what was updated and what not?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 09:17:36
quote:
Originally posted by Zoma

Damn....yes it was...but it was just only one culumn that i changed and only one string name...It is a parameter table. Just the server name,user and password....damn..


anyways in future try to do update inside transaction and check the results before you commit. At least that gives you a chance to review and if its unexpected you can always rollback
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:19:56
"Ok now...How to check how many updates were done in a database like as in today coz im really not sure? isnt there a script to check what was updated and what not?"

No - that's what FULL Recovery Model is for ... you can't get that information in Simple Recovery Model
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 09:21:17
But my script above will tell you what is different between NOW and your backup - if its just a Parameter Table chances are that no other updates were made to that column today.

(Just to check: Did you see my post "Posted - 02/05/2010 : 09:12:29" - you were typing your reply at the same time I reckon
Go to Top of Page

Zoma
Yak Posting Veteran

76 Posts

Posted - 2010-02-05 : 09:26:43
lol... :) ya Kristen i remember....well no worries now, The developers were using the wrong database for the application and thats the one i updated...ha ha ha ha ...so now gotta help em restore,change blah blah...but will let u know if i encounter any problems!!!!
Go to Top of Page
   

- Advertisement -