What's the best way to manage sql change scripts for two developers?

Question

Up until now I've been a lone wolf on my client projects. Anytime I would make changes to SQL Server: tables updates, stored procs, etc. I would generate the change script and plop it into a directory. When the application was ready for release, I would run the scripts on the live server and be done.

Soon I will have another developer working on the same project. The project files are all in source control. I'm just not exactly sure how to go about handling the change scripts. I'm guessing they should be under source control as well? If so, what would be the best naming convention? How exactly would I determine which scripts are to be executed on the next release? Keeping in mind that this is a fairly low-key, informal web project that does not have any version numbers or project management software.

Thanks.

Answer 1

Yes, you should put them in source control. The naming convention does not matter as much as being consistent with it does. One way is to append an artificial (create one) application version number in the filename of each script. We could probably give you better naming examples with if you give more detail. But, definitely you want them in source control.

Answer 2

We source control the change scripts as .sql files, then keep the order of execution for the sql files in a batch file, which is also under source control. The batch file calls OSQL with the sql file as a parameter:

SQLScripts.Bat:

SET BASEDIR=%%1
SET SERVER=%%2
SET DATABASE=%%3

CALL RUNISQLW CreateUserPresets %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW CreateFundWorkflows %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spFundWorkflowAddFromTemplate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spFundWorkflowListForGrid %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksListForGrid %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW fGetToleranceDate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW fGetNotifyDate %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksManager %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksAnalyst %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW spWorkflowTasksNotify %BASEDIR% %SERVER% %DATABASE%
CALL RUNISQLW AddGateFrequency %BASEDIR% %SERVER% %DATABASE%

pause

RUNISQLW:

@REM First Parameter: Name of SQL file, without the .SQL extension.
@REM Second Parameter: Base Directory to run the file in.
@REM Third Parameter: Name of the server to run the file on.
@REM Fourth Parameter: Name of the Database on the server.

osql -S %3 -d %4 -E  -i %2\%1.sql -o %2\Output\%1.txt

We then call the SqlScripts batch file from a deployment.bat file for each configuration environemt, Dev, Staging or Production. This keeps it consistent across configs.

Answer 3

I currently store my sql change scripts in a folder and name them, script order number, tablename, description of change

1-User-create-table.sql

2-User-added-columns.sql

...

n

When I've executed these scripts I move them into a new folder, named "release 2009-09-01" and and then continue with the next number