Recovering Data from a Backup with SQL Data Compare

I had a customer recently ask about how to recover data from a backup file. I believe the request was for SQL Backup, but I knew there was another way, and I suggested SQL Data Compare. We used to have an object level recovery tool, but that wasn’t very popular. However, SQL Data Compare can handle this task and I’ll show you how.

I’ve got a database, called Sandbox, and I take a backup of the database. After this, I delete some data, and I see then a few rows in a table. You can see 6 rows below.

2021-01-19 17_21_15-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (56))_ - Microsoft SQL Server

Now, I know there was more data in here. Imagine someone accidentally deleted data, and we want to get it back quickly. If I open SQL Data Compare, I can change the “Source” from the default, of a database, to a backup. I choose this from the dropdown near the “Source” text.

2021-01-19 17_18_22-(local)_SQL2017.SimpleTalk_1_Dev v (local)_SQL2017.SimpleTalk_5_Prod.sdc_

I can then add backup files. If I click the link, I get select a file from a file picker. Once I do this, I see this is a valid backup set. If needed, I could choose multiple files here.

2021-01-19 17_18_32-(local)_SQL2017.SimpleTalk_1_Dev v (local)_SQL2017.SimpleTalk_5_Prod.sdc_

Once I have this, I can then select my database in the Target. I’ll then click the Compare button.

2021-01-19 17_18_46-(local)_SQL2017.SimpleTalk_1_Dev v (local)_SQL2017.SimpleTalk_5_Prod.sdc_

Data Compare does it’s work and I end up with a list of identical, different, and missing objects. In this case, most everything is the same, but I have one object that is different. If I select the object, I see there are 4 missing rows.

2021-01-19 17_19_03-SQL Data Compare - E__Documents_SQL Data Compare_SharedProjects_(local)_SQL2017.

If I click the Deploy button, I get generate a script. This is the same process many people use with SQL Compare to generate code differences.

2021-01-19 17_19_14-Deployment

When this is done, I see my script contains a number of insert statements. If I’d changed some data, I would also see update statements here.

2021-01-19 17_19_20-Deployment

I can now run this script to recover my data.

This is a quick and easy way to recover data. I do need a primary key for Data Compare to work, one of many reasons to ensure your tables have PKs. This also works well up to low GBs of data, but if you have more than that, this might not work well.

I wouldn’t recommend this for every situation, but for many small “oops” problems, especially with lookup or smaller tables, this is a great way to recover missing data.

If you haven’t tried SQL Data Compare before, maybe you want to give it a try today.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.