Azure SQL Database
An Azure relational database service.
5,181 questions
Microsoft release a new, free tool: Database Compare Utility
Compare same Databases and Tables, one is present on the on-premise sql server and the other one present on the Azure cloud
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 47%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENP%3C/text%3E%3C/svg%3E)
Nilesh Patel
111
Reputation points
Want to compare the same databases and tables after the migration from the on-premise SQL server to the Azure SQL database.
Is there any script that can compare all the databases, tables, functions, stored procedures in one go? So that I can make sure all the things are migrated and none of the data is misplaced.
e.g., let say I have 5 rows and 6 columns in a table on on-premise SQL server and after the migration I want to check all the rows and columns are present in the target.
I have large numbers of databases and tables. So, Can't do this manually for each databases and tables, need to automate this so I can save time.
Thanks.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor2021-12-08T09:47:37.81+00:00 Hi NileshPatel-7672,
Do the answers help you?
If you find any post in the thread is helpful, you could kindly accept it as answer.
If you have any question, please feel free to let us know.Best Regards,
Amelia -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(204.8, 44%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESS%3C/text%3E%3C/svg%3E)
Saurabh Sharma 23,751 Reputation points Microsoft Employee2021-12-21T15:58:27.563+00:00 @Nilesh Patel Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.
Thanks
Saurabh
Sign in to comment
4 answers
Sort by: Most helpful
-
Olaf Helper 41,006 Reputation points2021-12-03T10:16:19.557+00:00 -
Nilesh Patel 111 Reputation points
2021-12-06T05:46:12.517+00:00 Can I do field to field and record to record comparison too?
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 71%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Homebrew9 26 Reputation points2021-12-23T12:48:46.417+00:00 Thanks for pointing out this utility, OlafHelper. I had a cursory look at it - seems like it compares hashes of each row for each specified table. And it requires some unique id - primary key or unique constraint on both tables. Wish we had this utility earlier. We have a major ongoing migration project in my organization and I wrote a similar utility in Python.
Definitely interested in how the Microsoft utility develops going forward. Thanks again!
Sign in to comment -
-
Alberto Morillo 32,896 Reputation points MVP2021-12-03T13:08:36.967+00:00 Allow me to recommend SQLDBDiff, this commercial tool that will simplify the post-migration validation test by comparing data and schema between source and destination databases. It supports SQL Server Version 2005 to 2019, SQL Azure and SQL on Linux. There is free version and a commercial version.
-
Nilesh Patel 111 Reputation points
2021-12-06T06:01:51.367+00:00 Can I do field to field and record to record comparison too?
Sign in to comment -
-
Erland Sommarskog 101.9K Reputation points MVP2021-12-03T23:03:09.68+00:00 In addition to the other suggestions, there is a very good database compare tool in Visual Studio, once you have installed SSDT, SQL Server Data Tools.
-
Nilesh Patel 111 Reputation points
2021-12-06T06:01:57.707+00:00 Can I do field to field and record to record comparison too?
Sign in to comment -
-
AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
2021-12-06T07:54:41.7+00:00 Hi NileshPatel-7672,
You can go to SSDT Tools menu ->SQL Server-> new data comparison to compare data (record to record) between SQL Server databases and Azure SQL databases; and new schema comparison to compare schema (field to field).
Please refer to Use Schema Compare to Compare Different Database and Compare and Synchronize the Data of Two Databases which might be helpful.
Best Regards,
Amelia
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.-
Erland Sommarskog 101.9K Reputation points MVP
2021-12-06T22:03:52.783+00:00 Thanks Ameila for filling. I did actually not know that there was a Data Comparison in Visual Studio. I only have had reason to use the Schema Comparison. And it wasn't clear to me that Nilesh wanted to compare both schema and data.
Re-reading Nilesh's question, I see that he wants to automate this, as there are many databases. I never had reason to compare more than one pair of databases at a time, so I don't know VS offers in this area.
Red Gate are famous for their Schema Compare and Data Compare, but same thing there. I don't know if they lend themselves easily to automation. (But any that has a command-line interface should do, I think.)
Sign in to comment -