Voice of the DBA

Category: Blog

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

  • Daily Coping 25 Jan 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to eat something healthy today that nourishes you.

    I think I’m in pretty good physical shape. At least, I do think all the exercise and work I do to take care of myself helped me with COVID last year.

    However, my diet isn’t great. I think it’s better than average sometimes, but maybe average American others. I don’t have too much of a sugar problem, but I do have a pizza, carb, etc. issue.

    My wife encourages me to be better, and she certainly has a better diet than I do. I find food comforting, and I have 5 decades of bad habits, but I am trying to be better. Having a daughter that needs gluten-free cooking helped a little, but it’s easy to be GF and have poor nutrition. However, I have done some ramen and pho dishes that are pretty

    In trying to be better, I decided to tackle some healthier recipes recently. I searched around, and found one for Chicken Parm Stuffed Peppers. I like normal chicken parm, but it’s not the healthiest. This was a better idea.

    It was good. Even to me.

  • The First Data Saturday is Tomorrow

    Data Saturday #2 – Guatemala is tomorrow. This is the first event on the calendar, though the second one to move from SQL Saturday over. I’m thrilled that this is taking place and that there are presentations in both English and Spanish. I hope sometime to actually attend a live event like this, and see a session in another language. I’m curious if I can follow along with the code and slides.

    If you haven’t registered, you still can and attend virtually.

    Unfortunately I won’t be here, as I have prior commitments all day, but I’m looking forward to hearing how to goes.

  • Daily Coping 22 Jan 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to take a different route when you next go somewhere.

    I go a lot less places than I used to before the pandemic. It’s something that never really mattered to me, but it stands out now. My Google travel timeline each month is way less diverse than I’d like.

    However, I do go to the grocer, the gym, and coach a little volleyball. Those are relatively short trips, but I can go out of my way a little. The other day, I was going to the gym, and normally it’s a fairly straight, short set of roads. However, I can go a little past my normal turn and turn into a neighborhood that loops back behind the gym. It’s a slower route through some neighborhoods, but it also goes alongside the E-470 highway outside Denver.

    It used to be a very open route, but there has been more development, and there’s less open space. The nature of the changing world, but not a welcome site. As many others feel, I wish the area was more like when I moved here.

    In any case, it was a nice drive, windows open, heat blasting, music turned up.