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.
  • Hi, can you please provide a quick timeline of when you restored, and when the normal backup job runs with dates please? – Hpk Apr 20 '16 at 8:09
  • @Hpk Sure. The backup from 2016-04-19-01h00m00s was restored first, by mistake, at precisely 18:36:27 the same day. There is a "log" backup with that timestamp inside the MSSQL Server folder. The latest backup file is from 2016-04-20-01h00m00s (today). My latest restore, which I described above, was performed today (April 20) at 4:30AM more or less. Thank you. – Marc.2377 Apr 20 '16 at 8:19
  • 2
    What kind of database is it? Will people be trying to log in and update records they added yesterday that have now disappeared from live? Can you take it offline whilst you fix things as best as possible? How financially or legally critical will any discrepancies be? – Martin Smith Apr 20 '16 at 8:37
  • So, you basically want to "replay" the changes that happened after the accidental restore (6:30) and until the correcting restore (10:00)? It might have been easier if you had stopped all other access to the db as soon as you realized the mistake. – ypercubeᵀᴹ Apr 20 '16 at 8:42
  • 3
    Sounds do-able then with a bit of luck but I don't think you'll find a magic easy solution. Redgate can help identify the different rows but you'll still need to determine whether these are inserts or updates and where there are identity column clashes ensure the new id is also used when copying over all child records. – Martin Smith Apr 20 '16 at 9:06

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

| improve this answer | |

First off... I think it is best to come clean to your manager on such issues. The business will notice the missing data and he will not appreciate being blindsided and not having all the facts at hand. In that event, things will be even worse for you. If you have no formal background in DBAing, you have no business doing DBA tasks and/or the real production DBAs ( if they even exist ) should have ensured that the business is protected against such human errors.

Simply put there is no way to restore individual rows auto-magically. There are tool set available ( Red Gate Data Compare springs to mind ) that allow you to compare data sets and build scripts of the differences, but it is upto you to decide which changes are good.

| improve this answer | |

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.