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:
- 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.
- 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.
- 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.
- 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.