SQL Server 2014 – How to Undo Accidental Restore

restoresql serverssms

Yesterday I made a serious mistake¹: Restoring the wrong database.

Background (you may skip this section):

I right-clicked the Test database and proceeded to the "Restore Database" section as usual. Then, when selecting the media (file) for the Source, I browsed for my latest production database backup (performed at 01:00 AM). This was not a mistake; I did need to have up-to-date information on the Test database in order to… test.

However, as soon as I hit OK on the "Add media" dialog, SSMS detected that my backup was made from the Production DB and "silently" changed the "Destination" field to reflect that. I didn't realize this until it was too late. All database changes from 01AM to 06:30PM were lost.

After a few hours struggling on how I could approach my manager and deliver these unhappy news, I decided I would try my best to repair the damage in any way possible, and for a certain extent, I luckily succeed.

Now, the steps taken:

  1. First and foremost, I made sure that today's backup had been performed, since it contained DB entries between 06:30PM to 10:00PM which could not be lost.
  2. Went to the "Restore" dialog for the Production DB, and then clicked the Timeline option hoping to perform a point-in-time restore at precisely 06:29PM.
  3. Unzipped the previous day backup (same one I accidentally restored previously) to my default backup location, and made sure a suitable "log backup" was present on my system.
  4. Set up the desired point in time, clicked "Verify Backup Media" and proceeded to backup.

Now, I'm relieved that it worked, but as expected, all of my tables contains records up to 6:29PM and everything else is missing. It's a large database with more than 100 tables, and hand-checking and inserting everything would take days.

Question: is it possible to "restore" only the missing records which are present on my latest backup, but not in my current state DB? If so, what are the steps necessary?

¹ "Not as serious as yours, I fear" – Bane

Edit in response to James Anderson concerns:

  • About the backup chain: Actually I do have a backup file with the changes from 06:30PM till 10PM. It was created by a Job I've set up. In fact I even restored this backup to a new temporary Database just to be sure.
  • About getting more people involved to help: My manager is travelling for next couple of weeks, my team is really small (3 people) and I'm the one in charge right now. Which is to say, I'll inform them, but I'm responsible for handling this alone. Even if manually. If there are any tools that could help please suggest. Thank you everyone.

Best Answer

Come clean ASAP. The sooner you do this the better.

If I'm reading the timeline correctly, when you restored the FULL backup over your production database you broke the backup chain. This means you wont be able to get the data for 06:30PM to 10:00PM into your current database that holds data for 01AM to 06:30PM + new data since the RESTORE.

I would suggest you RESTORE the database with 06:30PM to 10:00PM data and figure out a T-SQL code based way to move that data into your live database. You may have problems with IDENTITY columns as new rows in both databases have probably taken the same IDs.

IMO the quicker you get other people involved to build a plan to fix this the better.

Good Luck

Related Question