Check all records were migrated to the destination

Question

Check all records were migrated to the destination

Oscar_Boots

Old Hand

Points: 362
More actions
August 19, 2020 at 1:08 am
Go to Answer
#3781481

Hi Forumers,
I have some data (many tables) that will be mapped to a new SQL Server Database.
I need to ensure all records from the source actually appear in the new DB.
Can someone advise the best method to do this?
Would this be best performed in SSIS or can it be done in SSMS 2014?
Thanks in advance.
Grant Fritchey

SSC Guru

Points: 398690
More actions
August 20, 2020 at 10:03 am
Answer
#3781887

Not that I'm aware of. There are example queries around here on SSC if you look for them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

The number of tools that will let you move things is huge. SSIS will work. So will using the import/export in SSMS. Can you just start with a backup & restore? That will be a lot easier than any export/import process. Otherwise, you can explore those choices or any of the other mechanisms for Extract/Transform/Load.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Oscar_Boots Old Hand Points: 362 More actions · Answer 2

Thanks Grant,

This is more about ensuring that ALL records have reached the SQL Server destination DB.

What can you suggest for this purpose?

Is there a SQL Server Compare type tool I can use?

Thanks Peter

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

If you do a backup & restore, it's a page-by-page copy. If the restore is successful, there's nothing to check. Everything moved.

However, yes, if you use any of the other migration methods, there are a couple of checks. First, almost every one of them will include errors and warnings. So if some rows were skipped for some reason, you should know it. Second, there are compare tools. I work for a vendor that makes a pretty solid one that I've used for about 20 years now (for 11 years before I joined the company). Redgate SQL Data Compare. That will tell you exactly what you're looking for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Oscar_Boots Old Hand Points: 362 More actions · Answer 4

Thanks again,

I've seen this product before and it looks like it's a good fit for the job but I have no budget.

Is there a free included product that can be given a Table from the Source & a Table from the Destination and produce a report or result to say that the data is identical?

Thanks again.

Oscar_Boots Old Hand Points: 362 More actions · Answer 5

Oscar_Boots

Old Hand

Points: 362

August 20, 2020 at 10:06 am

#3781889

Thanks Grant,

Appreciate your time.

Peter