SQL Server mistaken update rows without backup

Question

I did a mistake UPDATE query yesterday. Database engine is MSSQL and the version is 2017.

I have not any backups. All I have is the database itself. Do you know any ways to redo my modifications to one second before the time which the damn query was triggered?

In addition, I tried transaction logs but every time I want to restore database to time before accidentally UPDATE I face to this message: No packupset is selected in SQL Server. I think it's because my TLog has not period of time due to I dad not backup any transaction log before.

Answer 1

DISCLAIMER
Please be advised that these steps are by no way complete and do not guarantee a restore of your database and/or data. Please use them as a guideline in your specific situation and be aware that a multitude of unknown causes, could result in complete data loss.

Step 1.) and Step 2.) can be used in any situation

Further additional steps may be required to bring your data back.

Welcome to DBA.SE. Sorry to hear you're having a bad time.

Handling Your Current Situation

If FULL Recovery model is enabled for this database do the following:

1. Stop all transactions/connections to the database. NOW !!

2. Think about your next steps.

3. Take the database OFFLINE and make a copy of the *.MDF and *.LDF files.

4. Bring the database back ONLINE.

5. Perform a Transaction Log Backup (TLOG backup) with the option WITH ... NO_TRUNCATE .... This ensure the data isn't deleted from the TLOG. This step will only work if the database has previously been backed up with a FULL backup

6. Restore database to point-in-time on a different database server using the initial FULL backup (before step 1.) and TLOG backup from the second step. The time should be before the accident happened.

7. Search for the modified data.

8. Extract data.

9. Import data into production server after confirming it is correct.

10. Bring production back online.

If you didn't have the database in FULL recovery model, then changing it now will have no impact. You won't be able to rescue the data, because it probably has already been committed to the database and is no longer available in the TLOG file. If no initial FULL backup has been performed, then these steps won't work.

Cleaning Up

1. Talk to management about what happened.
2. Implement a Change Management process.
3. Implement a solid backup procedure for your database systems.

DBA Tips

Here a couple of basic tips:

1. Always think about your next steps.
2. Ensure you have a backup, before you modify data.
3. If the data is important/productive, ensure you have FULL recovery model enabled and Transaction Log backup is being performed.
4. Only change data in a productive environment after management has told you to do so and if you have an official Change Request that has been signed off.
5. Never touch productive data manually, unless it is during a down-time and you have met the requirements for 1. and 2. and 3.

Answer 2

I had a similare case some years ago, but i was deleting some records. I was able to make some application to read the raw database file and read out the deleted data. A lot of work and a lot of studying the content with an hex editor. I was able to recover everything. So if its really important, you can start by using a hex editor to open the database file (make a copy) and se if you can find the lost data, if you can find it, you'll have to find how columns and rows are stored and make a program to read it and store it some way you can use the data. Good luck.