Should database files(scripts etc.) be on source control?
If so, what is the best method to keep it and update it there?
Is there even a need for database files to be on source control since we can put it on a development server where everyone can use it and make changes to it if needed. But, then we can't get it back if someone messes it up.
What approach is best used for databases on source-control?
Best Answer
Yes. You should be able to rebuild any part of your system from source control including the database (and I'd also argue certain static data).
Assuming that you don't want to have a tool to do it, I'd suggest you want to have the following included:
All scripts should include the appropriate drop statements and be written so they can be run as any user (so including associated schema / owner prefixes if relevant).
The process for updating / tagging / branching should be exactly as the rest of the source code - there's little point in doing it if you can't associate a database version with an application version.
Incidentally, when you say people can just update the test server, I'm hoping you mean the development server. If developers are updating the test server on the fly then you're looking at a world of pain when it comes to working out what you need to release.