What is the best way to track any change made to the SQL Server database before moving to production in case of a large team ? We have used excel sheets but quite often things get missed which come out with SQL compare only during moving changes to production?
SQL Server Data Tools provides a Visual Studio project template which allows database development. This way, you can have the source of your database within the same solution that contains the code for your application. You can track all the changes made to the database through source control, given that all changes are made through the project.
It also has a database compare tool, in which you can see the structural differences between the database and what's in your project. The tools also make it easy to deploy the changes made in the source code to the database.