为两个开发人员管理sql更改脚本的最佳方法是什么？-What'sthebestwaytomanagesqlchangescriptsfortwodevelopers?

1  

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

我目前将我的sql更改脚本存储在一个文件夹中并命名它们,脚本订单号,表名,更改说明

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

当我执行这些脚本时,我将它们移动到一个名为“release 2009-09-01”的新文件夹中,然后继续下一个数字

5  

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.

是的,你应该把它们放在源代码管理中。命名约定与其一致无关紧要。一种方法是在每个脚本的文件名中附加一个人工(创建一个)应用程序版本号。如果您提供更多详细信息,我们可能会为您提供更好的命名示例。但是,你肯定希望它们在源代码控制中。

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:

我们将更改脚本控制为.sql文件,然后将sql文件的执行顺序保存在批处理文件中,该文件也受源代码控制。批处理文件使用sql文件作为参数调用OSQL:

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.

然后,我们从deployment.bat文件中为每个配置environemt,Dev,Staging或Production调用SqlScripts批处理文件。这使得它在配置中保持一致。

1  

An important component I would suggest addressing is ordering - the files should include (perhaps at the beginning) some kind of sortable date & time stamp. This way you can accurately test and debug the order that the scripts are executed in.

我建议解决的一个重要组件是订购 - 文件应包括(可能在开头)某种可排序的日期和时间戳。这样,您就可以准确地测试和调试脚本执行的顺序。

0  

If you are using VS Team Edition you can use the database edition to create a database project for the sql server version you are using.

如果您使用的是VS Team Edition,则可以使用数据库版本为正在使用的sql server版本创建数据库项目。

Then, build the project from the database, so you get all the functions, views, tables into the project.

然后,从数据库构建项目,以便将所有函数,视图和表格放入项目中。

Then, whenever you make changes, make it in the project, so it can be in svn easily (each file is there) and then you can just sync up your sql server database.

然后,无论何时进行更改,都可以在项目中进行更改,因此可以轻松地在svn中(每个文件都在那里)然后您可以同步您的sql server数据库。

This way you can update your database from changes your teammate made without messing up your data.

这样,您可以根据队友所做的更改更新数据库,而不会弄乱数据。

0  

Start learning how to use branches with your source control. Which can be very valuable when working collaboratively.

开始学习如何在源代码控制中使用分支。在协同工作时,这可能非常有价值。

Some branching strategies can be:

一些分支策略可以是:

• Name the branch after the developer (and each developer on the project will then be responsible for keeping their branch in sync with the mainline).

在开发人员之后命名分支(然后项目中的每个开发人员将负责使他们的分支与主线保持同步)。

• Create a new branch for every feature and then delete the branch once you have merged it back (Only really feasible with a distributed version control system)

为每个功能创建一个新分支,然后在将其合并后再删除分支(仅对分布式版本控制系统真正可行)

0  

Have a look at the patching engine used in DBSourceTools.
It's been specifically designed to help developers get SQL server databases under source-code control.

看看DBSourceTools中使用的修补引擎。它专门用于帮助开发人员在源代码控制下获得SQL服务器数据库。

This tool will allow you to baseline your database at a specific point, and create a named version (v1).
Then, create a deployment target - and increment the named version to v2.
Add patch scripts to the Patches directory for any changes to schema or data.
Finally, check the database and all patches into source-code control, to distribute with devs.

此工具允许您在特定点建立数据库基线,并创建命名版本(v1)。然后,创建部署目标 - 并将命名版本增加到v2。将补丁脚本添加到Patches目录,以获取对架构或数据的任何更改。最后,检查数据库和所有补丁到源代码控制中,与devs一起分发。

What this gives you is a repeatable process to test all patches to be applied from v1 to v2.
DBSourceTools also has functionality to help you create these scripts, i.e. schema compare or script data tools.

这给您带来了一个可重复的过程来测试从v1到v2应用的所有补丁。 DBSourceTools还具有帮助您创建这些脚本的功能,即模式比较或脚本数据工具。

Once you are done, all of the files in your patches directory become your release, and will upgrade your database from v1 to v2.

完成后,补丁目录中的所有文件都将成为您的版本,并将您的数据库从v1升级到v2。

Have fun.

0  

Another way is to have a tool like http://www.red-gate.com/products/SQL_Compare/index.htm

另一种方法是使用像http://www.red-gate.com/products/SQL_Compare/index.htm这样的工具

It generates a change script for a single deployment. The good thing is that you don't have to count on the discipline of developers anymore.

它为单个部署生成更改脚本。好消息是你不必依赖开发人员的纪律了。

Apart from that, I still like to have the DB in SVN. I use SQLScript for that, see free utility to script DB objects in ms sql.

除此之外,我还是喜欢在SVN中使用DB。我使用SQLScript,请参阅ms sql中的脚本DB对象的免费实用程序。

EDIT

I now use Redgate SQL Version. Its pretty easy to put the changes into SVN with it. The only problem is that I have to start and use the Application, its not an automated process.

我现在使用Redgate SQL版本。使用它很容易将更改放入SVN。唯一的问题是我必须启动并使用应用程序,它不是自动化过程。