How to recover SQL Server data from accidental updates

Question

Environment: MS SQL Server 2088 R2 
Tools: MS SQL Managment Studio 
Problem: The accidental update operation took place on Friday 17/10/2014 around 3:30pm. How to recover SQL Server data from accidental updates. Please advise with supportive examples

Answer 1

Please see this link: SQL Server 2008 Service Pack 1 (SP1) Uninstall

---

T-SQL Articles
T-SQL e-book by TechNet Wiki Community
T-SQL blog

Answer 2

Do you have full backup and log backups?

you can restore to point of time with your database backup files.

Answer 3

Don't waste time on how to get these lost data back.

Since this database does not has any backup, you don't consider it as important in the first place. If you do lose something important, take this as the tuition for a valuable lesson. Backup all your databases and make sure you test your backups and confirm they are working from time to time.

Answer 4

If there is no backup of the database, you will need to look for other sources for finding the data. Maybe the data comes from written or printed records? Maybe people have it their heads?

Your best hope be that the DBA says that he thinks there is no backup. Maybe he can do some better research? There are tables in the msdb database which holds this information. (I don't recall the name right now.)

(As for backing up from paper - I have been involved with a few crashes where the backup was old, but users were able to recover all information from logs, filed paper information etc.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Accidental UPDATE is in the same category as accidental DELETE.  As noted above you need a backup to recover.

The simplest protection is plan preparation by the Database Maintenance Plan Wizard:

http://msdn.microsoft.com/en-us/library/ms191002.aspx

---

---

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014

Answer 6

There are tables in the msdb database which holds this information. (I don't recall the name right now.)

It's the table backupset (Transact-SQL)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Thanks for all support. It has been resolved by accessing to an old backup. I was able to recover all the data using match statement. I had to look up for other resources.