SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Rolling Back the update!!!! Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zoma
Yak Posting Veteran

South Africa
76 Posts

Posted - 02/05/2010 :  08:52:43  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 02/05/2010 :  08:56:52  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/05/2010 :  08:58:36  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 02/05/2010 :  08:59:24  Show Profile  Reply with Quote
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 - 02/05/2010 :  09:03:16  Show Profile  Reply with Quote
WHAT IF HE HAD NOT TAKEN ANY BACK UP BEFORE UPDATING?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:07:10  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:07:53  Show Profile  Reply with Quote
"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

South Africa
76 Posts

Posted - 02/05/2010 :  09:07:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/05/2010 :  09:09:59  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:12:29  Show Profile  Reply with Quote
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

South Africa
76 Posts

Posted - 02/05/2010 :  09:13:03  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:15:35  Show Profile  Reply with Quote
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

South Africa
76 Posts

Posted - 02/05/2010 :  09:16:57  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/05/2010 :  09:17:36  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:19:56  Show Profile  Reply with Quote
"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

United Kingdom
22415 Posts

Posted - 02/05/2010 :  09:21:17  Show Profile  Reply with Quote
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

South Africa
76 Posts

Posted - 02/05/2010 :  09:26:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000