I recently took over the maintenance of 10-15 SQL Server databases and to my surprise found out that the team does not store the code scripts anywhere. They currently deploy changes to each environment without any paper/code trail.
The code for applications that use these databases are all stored in TFS and I need to implement a directory structure for storing the scripts – tables, views, stored procedures – in TFS.
On our Oracle side of the house we use a two directory structure:
- ddl
- sql
But I think there could be improvement in storing the scripts on the SQL Server side. I was thinking of using something similar to the way SSMS stores objects:
- tables
- views
- stored procedures/functions
Is there a standard structure or best practice that is used to store SQL scripts?
Best Answer
Storing them in a similar structure to what you see in SSMS is a good way to do it.
Do you have visual studio licenses with your TFS licenses? The "Database Project" features in Visual Studio are fantastic for organising database projects. It can import the schema from a live database, and then it stores all the schema objects as scripts, under a folder structure that is analogous to what you would see in SSMS. There are also pre and post deployment script locations as well.
The benefit of this approach is that you can include the database project within solutions, and have builds which deploy the database etc (quite useful when dev'ing on a local machine).
You can also then get visual studio to create scripts to take a given schema (ie production schema extracted as a dbschema file), and bring it into line with the schema in TFS. Very handy for deployment scripts.