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.

  • 4
    No backup - no revert. The only way to restore some previous DB state is to restore backup before timepoint in question, then repeat transactions from backup time till this timepoint. Transaction log do not store what data was stored in a table before transaction and was altered/deleted by a transaction. You have no backup. So your data loss is now permanent. – Akina Feb 18 '19 at 5:50
  • @Akina Then I think it's time to exit the office. Because the data was so important. At least I faced an awful experience which should not be repeat ever. – Shojajou Feb 18 '19 at 6:00
  • 1
    What backups do you have available? Was the database in SIMPLE recovery mode while running the bad UPDATE? – John Eisbrener Feb 18 '19 at 15:16
  • @JohnEisbrener We have no backups. The database was in FULL recovery mode while the mistake was happened. – Shojajou Feb 19 '19 at 5:47

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.
  • Thanks for your advices and tips. I have tried these steps already but at step #4 i am forced to recover to time point of when the backup was taken. Btw It is the first time i have made a backup of this database i wonder if it is related to having no previous backups or it is something else. tnx – Shojajou Feb 18 '19 at 9:42
  • You wouldn't have been able to perform a TLOG backup if no previous backup has been performed. It fails. – John aka hot2use Feb 18 '19 at 10:10
  • I had temporarily deleted this answer, because I had some doubts about the order and importance of each step. I have verified the steps and added comments to leave adequate room for improvements. I included a new step 2. Think about your next steps.. – John aka hot2use Feb 18 '19 at 14:48
  • Thank you. I'm waiting for your helpful solutions. It is so important for me to solve this stupid mistake. – Shojajou Feb 18 '19 at 14:52

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.