Tag: Redgate

This is a post that looks at how to compare data changes in recent data. A customer recently asked me about looking at a table, and choosing specific data to compare. In this case, the data they were looking to compare was the most recent data.

Scenario

I decided to set up a quick scenario to showcase this for the customer. I created a table that has some data:

CREATE TABLE [dbo].[DataWithTime](
     [myid] [int] IDENTITY(1,1) NOT NULL,
     [Mydata] [varchar](20) NULL,
     [mytime] [datetime] NULL,
  CONSTRAINT [DataWithTimePK] PRIMARY KEY CLUSTERED 

(
     [myid] ASC

)

GO

INSERT INTO dbo.DataWithTime (Mydata, mytime) 

VALUES

( 'A', N'2021-01-22T12:42:33.213' ), 

( 'B', N'2021-01-22T12:52:33.213' ), 

( 'C', N'2021-01-22T13:02:33.213' ), 

( 'D', N'2021-01-22T13:07:33.213' ), 

( 'E', N'2021-01-22T13:12:33.213' )

I put this in my sandbox database. I wanted a second copy of this same table, but with less data, in another database. I edited the insert statement to look like this:

INSERT INTO dbo.DataWithTime (Mydata, mytime) 

VALUES

( 'A', N'2021-01-20T12:42:33.213' ), 

( 'B', N'2021-01-21T12:52:33.213' ), 

( 'CC', N'2021-01-23T13:02:33.213' ), 

( 'D', N'2021-01-24T13:07:33.213' ), 

( 'EE', N'2021-01-25T13:12:33.213' ),

( 'F', N'2021-01-26T13:07:33.213' )

Now I have two copies of my table, with disparate data. What’s different?

Data Comparison Filters

If I open SQL Data Compare, you get the default comparison. I’ll set this up with my two test tables:

When I do the comparison, I see the differences between the tables. As you can see, I edited two rows and added one.

That’s great, and in a table of a few rows, this isn’t an issue. What if this table has a million rows? Or a billion? I don’t want to scan everything.I want to limit things.

I can, if I click “Edit Project”.

and then choose Tables and Views. I’ll see my table listed.

I can select the row with my table, DataWithTime, and then I can click the “Where clause” link in the upper right.

This pops up a dialog where I can enter a WHERE clause to be used for the table. I can set the same clause for both the source and target, or use separate ones. I’ll use the same one here.

I can click OK for this and then Compare now to re-run the project. This gives me the data compared, but without looking at any data before the 25th of Jan. Notice only two rows below instead of 3.

Am I sure this still didn’t impact my SQL Server with a large query? This works great with 5 rows, but what about 1billion? Well, I ran the XEvent Profiler while I was editing the project, and then filtered this down to the SQL tools. When I do that, I see this:

The query being issued has my WHERE clause, which filters out data at the query processing level. This doesn’t guarantee a seek or limited reads, but if I have the column indexed, then I would get an efficient a plan as I could get.

SQL Data Compare is fantastic tool for finding data differences. Comparing large volumes of data can be slow, but if you use filters, you can dramatically speed things up. If you haven’t tried SQL Data Compare, download an evaluation today and see what you think.

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.