If you haven’t heard of SQLCompare by RedGate, you’re missing out. It’s an amazing product. In summary, you can do the following (which I use it for 99% of the time):
- Compare Schema / TSQL code from Different Databases
- Sync Schema / TSQL code across two databases
- Generate TSQL code from your comparison so you can use it for deployments
- Generate reports on SQL Changes
Most of those things can be done via the GUI version of the tool. The product also comes with a command line version, which you can use it in your build script.
To script out your entire database via the command console, you can do the following:
SQLCompare /force /database1:YOURDATABASENAME /username1:sa /password1:password /server1:YOURDATABASESERVERNAME /makescripts:c:\x
To compare two databases and generate a report via the command console, you can do the following. The reason there’s so many switches is because you need to enter the database name and credentials for the two databases.
SQLCompare /force /database1:DB1NAME /username1:sa /password1:password /server1:SERVER1NAME /database2:DB2NAME /username2:sa /password2:password /server2:SERVER2NAME /report:c:\report.html /reporttype:Interactive