Abstract:
Many data breaches come about from less secured development and test databases. Redgate has integrated SQL Data Catalog and Data Masker to allow you to continuously improve your classification and protection process for those environments. In this session Steve Jones will show how this process can be refined over time and distributed across a team of individuals in your organization.
Length: 60-75 minutes
Slides: ContinuousClassify.pptx
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.
While I have found that some aspects of my job with Redgate improved with our move to remote work this year, I think others have deteriorated. I have spent lots of time over the years trying to build relationships with people in lots of different departments. This year, I’ve gotten lots of time and more communication with those I often work with, but the connections across groups have been limited. I have worried this is affecting the great culture at Redgate.
We have a group of people who spend time trying to deepen and strengthen team relationships. They have a budget each year and typically try to plan different types of activities for teams, departments, and groups inside the company. During the pandemic, we’ve had some competitions (quizzes, exercise goals, best picture), and they’ve been good, but for our end of year celebration, they really did a fantastic job.
First, I saw Kendra post a picture of her end of advent calendar. Interesting, but I wasn’t sure what I thought. Plenty of other Redgaters posted similar pictures, which intrigued me, as I hadn’t gotten anything.
Then I got a package with a note not to open anything before Dec 1. It contained my advent gifts, 24 boxes, and I spread them out on a table. I opened the first few myself before my family got interested. Day 3 was a mug, which my wife promptly grabbed, washed, and filled. Since then I’ve been looking forward to seeing what I get each day, with my gifts slightly different than what my UK colleagues are posting each day.
Each year we usually have a large award ceremony, fancy dinner, and dancing with partners and colleagues. We can’t do this year, which I had expected to be a big loss when each office gets the chance to celebrate together. We’re separated, but this gift was an excellent substitute. Some teams jointly together get to celebrate our individual moments of joy with co-workers, bringing us together in a unique way when we can’t be together as a group.
Culture is hard to build and maintain, but Redgate did a fantastic job, despite the challenges. Hopefully some of you find your organizations trying to do something that brings you together as well.
Steve Jones
Listen to the podcast at Libsyn, Stitcher or iTunes.
Recently I wrote about ignoring comments in SQL Compare. That seems like something I want to do in all my projects, so I went looking for how to set this as a default. It wasn’t obvious to me, but since I can ping the Redgate Software developers and support staff, I found an answer for this post.
If I start SQL Compare by clicking a project, it opens and my settings are there. When I start SQL Compare, it brings up the New Project dialog, shown here:
However, if I don’t want a new project, and close that, I have a basic interface. Going through the menus, I don’t see any way to set global options.
There are Application Options, but these aren’t anything to do with projects.
However, I realized that I missed something a support person pointed out to me. On the options tab for any project is a button that says “Save as my defaults”.
I can set things from any project, or I can click the “My Projects” button in the toolbar, which gives me a list of projects (I don’t save many).
At the bottom of this is an “edit” button, which opens the familiar project dialog. I can click Options from there and see options.
When I do that, I can now set the items that I care about. The Ignore Comments is one, but there a few others I think cause issues. I do want to ignore encryption objects. I shouldn’t have the same ones in dev as prod, so I’ll check that. Others:
I can then click the “Save as my defaults”. This will ensure new projects have these options. If I have old projects I want to update, I can always click the “My defaults” in the project to load what I’ve saved. This animation shows how the button works.
Now I can easily ensure that projects work as expected on my machine. Unfortunately, these defaults are stored in the registry, so this is a client by client basis, but you can ensure all your projects are set up the same by default.
SQL Compare is a fantastic product to make it easy to see what has changed in a database. If you’ve never used it, give it a try today.
