0

I was meant to update a single column of a record in th database but i forgot to specify the id and every single record has now been updated! is there a way i can roll back the data please help!

I was meant to run the following statement :

Update Table set Cusname = "some name" where id = 2;

but i actually ran the following:

update Table set  Cusname = "some name"

now every single cusname column has the same name . please help Please help !

Rion Williams
  • 74,820
  • 37
  • 200
  • 327
1future
  • 255
  • 5
  • 21
  • 1
    Kindly ask the DBA to restore. (Been there, done that...) – jarlh Jun 08 '16 at 13:02
  • sorry but how do i do that? – 1future Jun 08 '16 at 13:03
  • 1
    Is this a production system? Do you have a back up that you can restore from? – DB101 Jun 08 '16 at 13:05
  • You don't have a DBA nor a backup? There is no roll back feature. – paparazzo Jun 08 '16 at 13:06
  • And thus you learn the importance of backups. The hard way... – jleach Jun 08 '16 at 13:07
  • 1
    Might want to look at questions like this http://stackoverflow.com/questions/12472318/can-i-rollback-a-transaction-ive-already-committed-data-loss – tarheel Jun 08 '16 at 13:09
  • 1
    Do you have any logging? – jarlh Jun 08 '16 at 13:10
  • @tarheel, that question is for postgres. Although, I agree that killing the server until a backup can be found is a good start. – JNevill Jun 08 '16 at 13:12
  • If the database is in Full Recovery Mode then the data is in the transaction log but hacking into the transaction log is not easy. – paparazzo Jun 08 '16 at 13:12
  • re: txn logs, see here: http://stackoverflow.com/questions/20978714/reverse-changes-from-transaction-log-in-sql-server-2008-r2 – jleach Jun 08 '16 at 13:12
  • You might find Temporal ata Tables useful in the future (SQL 2016 +). http://www.sqlshack.com/new-features-in-sql-server-2016-temporal-data-tables/ – DB101 Jun 08 '16 at 13:13
  • @JNevill I'm well aware, that's why I said questions **like** this to start the OP on the right path, and not stating that it was going to be the exact solution for the OP. – tarheel Jun 08 '16 at 13:13
  • 1
    is this a production database? Are there backup jobs in place? – RDJ Jun 08 '16 at 13:14
  • Thank you for your help people .. Luckily i managed to retrieve yesterdays snapshot from the server ! – 1future Jun 08 '16 at 15:18

1 Answers1

3

There's not much you can do... this is why you have a good backup strategy in place (and, ideally, don't execute any "ad hoc" t-sql in a production database before testing in a test database, then copy/paste the statements to help avoid these types of errors in the future).

Pulling info from the comments, you can start off by doing something along these lines: Can I rollback a transaction I've already committed? (data loss)

(this is for PostgreSQL, but the idea is the same... stop the server, backup all relevant files, etc).

If you have transaction logging and log backups, you can attempt a point in time restore, but this must be set up prior to your error. See here: Reverse changes from transaction log in SQL Server 2008 R2?

Your best bet in this case may be to spend some time working on resolving without restoring. It looks like you updated your customer names. Do you have another source for customer information? Can you compile an external list of customers and, say, addresses, so you can do a match on those to reset your db's customer names? If so, that might be a much easier route, getting you most of the way to a full recovery of your bonked field. If you don't have a ton of data, you can do this and fill the rest in manually. It's a process, but without a good backup of the db to revert to, it may very well be worth looking at...

Also note that if the database is hosted on a cloud based S/P/Iaas, you may have deeper level backups, or in the case of "SQL Database" (e.g., SQL Azure), point in time backups are set up out of the box even for the lowest service plans.

Community
  • 1
  • 1
jleach
  • 7,410
  • 3
  • 33
  • 60
  • Bad plan. If by some luck it is in full recovery mode a full backup would actually clear the transaction log. – paparazzo Jun 08 '16 at 13:43