Handling database changes/scripts in .NET?

I have recently started a new 开发者_Go百科job where they do most everything via Stored Procedures. We currently have a "deployment" folder under source control that contains timestamped folders with database change scripts (we actually have 3 folders under that - one for table modification scripts, one for views and functions, and one for sprocs), as well as a "Next" folder that has the new changes we are currently working on (it gets renamed when we do a deployment). We have three databases set up: a local copy on our workstation which is only accessible to the individual developer, a development DB and the live DB in production.

This means that we create and commit the .SQL files, and then have to manually run them on an almost daily basis (as new ones are added to source control, so pretty much every time we do an update we need to check what's changed in those folders and run them against our local copy of the DB), not to mention having to do the same thing on the dev and prod servers when we do deployments; also we have the databases named differently on each server to avoid accidentally running a change script in the wrong environment (this strikes me as odd as typically you have the DB instance named differently but the actual database named identical across all your servers) so we cannot include the USE statements in the scripts. This process seems very inefficient.

Is there a recommended best practice way of handling this kind of thing that I could suggest we start using instead?

I've been using a Visual Studio Database Project to handle this for about a year now. It's got a little learning curve, and it's not completely without hiccups, but it's definitely better than trying to manage this process manually.

As others have indicated, you should plan on factoring in a pretty heavy dose of "does it handle our situation" along with general best-practices. In my case, I've got multiple development branches that feed into an integration environment, along with some pretty restrictive DBA-type folks. Both of these have caused some complications along the way, and I expect you'll run into stuff like this, too.

Check out http://www.red-gate.com/products/sql-development/sql-compare/

You can use this tool to compares databases and create a script for the differences or synchronize the databases right aways

SQL Source Control is the tool we'd recommend at Red Gate for you to share changes between developers. This integrates into SSMS, and works with your existing source control system. SQL Compare is best used for deployments, rather than frequent use cases such as sharing changes in a development environment. If SQL Source Control wouldn't work for you for whatever reason, we'd love to hear from you.