SQL Server comparing mechanism

Question

1. I have a creation script for a SQL Server database and beside it I have a test script

2. My test Script should compare the result of my original script and the database and let me know if it is a match

3. I need to store the resulting script in a repository.

I've already got a script and the database. It's not hard to write the test script, but I want an app that automates the process.

I've already got Redgate tools and I heard it can make the job for me, but how? If not, are there any other tools that could do such a thing?

Answer 1

I'm not sure if you are searching something to compare your data or the structure of your databases.

If it's for the structure (like Red-Gate SQL Compare and not SQL Data Compare), you can install SSDT (Microsoft SQL Server Data tools - It's free and available for visual studio).

With SSDT you will be able to create a database project and compare 2 databases structures.

Here is a quick presentation that I found on youtube:
https://www.youtube.com/watch?v=FKSsbWnFMD8

Answer 2

If you're looking to compare the structure of the two databases this can be done using Redgate SQL Compare.

1. Set the location of your script as a "Scripts Folder" source within the tool.
2. Set the database where you have run the script as the target.
3. Run the comparison. This will show you if there are any differences.
4. Generate a report of the differences which can be referred back to at a later date. (https://documentation.red-gate.com/display/SC12/Generating+a+report)

To automate this process you can make use of the SQL Compare command line. (https://documentation.red-gate.com/display/SC12/Simple+examples+using+the+command+line)

Full disclosure: I'm a developer at Redgate for SQL Compare.

Answer 3

You can completely automate comparison and reporting with Apex Diff. Not sure which SQL Server are you using, but it's free for SQL Server Express.

Here is a step by step tutorial, which you should follow with one difference - set Script folder as data source: https://solutioncenter.apexsql.com/how-to-keep-a-source-control-repository-updated-with-the-latest-sql-server-database-changes/