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.
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.
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.
Once I have this, I can then select my database in the Target. I’ll then click the Compare button.
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.
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.
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.
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.