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- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
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) |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-02-05 : 08:59:24
|
Here is a script for a point-in-time restoreRESTORE DATABASE DBUtil FROM DISK = 'C:\Backup\dbutil.bak' WITH NORECOVERYRESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_1.trn' WITH NORECOVERYRESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_2.trn' WITH NORECOVERYRESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_3.trn' WITH RECOVERY, STOPAT = '2010-02-05 12:02:55' - LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
rajpes
Starting Member
13 Posts |
Posted - 2010-02-05 : 09:03:16
|
WHAT IF HE HAD NOT TAKEN ANY BACK UP BEFORE UPDATING? |
|
|
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. yyyymmddFROM 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. yyyymmddFROM MyLiveDB.dbo.MyTable |
|
|
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) |
|
|
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? |
|
|
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? |
|
|
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.MyColumnFROM MyTable AS DEST JOIN MyTempDatabase.dbo.MyTable AS SRC ON SRC.PrimaryKeyID = DEST.PrimaryKeyIDWHERE 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 |
|
|
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.. |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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!!!! |
|
|
|