SQL Server，在多个数据库上执行批处理T-SQL脚本
我们的 SQL Server 2000 实例托管着多个相似的数据库，每个客户对应一个数据库。当需要更新它们时，我们使用 Red Gate SQL Compare 在开发数据库和所有客户端数据库的当前状态数据库副本之间生成迁移脚本。
SQL Compare 生成一个事务性脚本，如果一个步骤失败，该脚本将回滚所有内容。但目前我们的系统使用一种方法，将脚本拆分为批处理分隔符（GO 语句），然后单独运行每个命令，这会破坏所有事务性内容。通过编程（在经典 ASP 中）查询数据库时不支持 GO 语句
我想知道如何以编程方式在所有这些数据库（如 250 DB）上运行该脚本（保留事务）或者在工具中手动？在查询分析器中，我们需要选择每个数据库并按运行，这对于我们拥有的数据库数量来说相当长。
Our SQL Server 2000 instance hosts several databases which are all similar, one for each of our client. When comes the time to update them all, we use Red Gate SQL Compare to generate a migration script between the development database and a copy of the current state DB of all the clients database.
SQL Compare generates a script which is transactional, if one step fails, the script rolls back everything. But currently our system uses a method that splits the script on batch separators (the GO statement) and then runs each command separately, which ruins all the transactional stuff. The GO statement is not supported when querying the database by programmation (in classic ASP)
I want to know how I could run that script (keeping the transactions) on all those databases (like 250 DB), programmatically or manually in a tool? In Query Analyzer, we need to select each DB and press Run which is quite long for the number of DB we have.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果您可以从 SQL 2005 或 2008 使用 SSMS，那么我推荐免费的 SSMS 工具包
If you can use SSMS from SQL 2005 or 2008, then I'd recommend the free SSMS Tool pack
我使用外部 sqlcmd 命令行工具。我工作的服务器上也有同样的情况。
我的脚本位于 *.sql 文件中，数据库列表位于第二个文件中。我有一个小的 *.bat 脚本，它遍历所有数据库并使用 sqlcmd 命令执行脚本。
更详细地说，我有这样的：
命令行看起来更像这样：
在 SQL Server 2000 中，它是 osql 实用程序
更新
Red Gate 现在有一个名为 的工具SQL Multi Script，它基本上完全符合您的要求。我支持 SQL 2000 到 2008 R2 并在多个数据库上并行运行查询，从而提高性能。
I use external sqlcmd command line tool. I have the same situation on the server I work.
I have the script in *.sql file and the list of databases on the 2nd file. I have small *.bat script which iterate through all the databases and execute script using sqlcmd command.
In more details I have like this:
The command line looks more-less like this:
In SQL Server 2000 it was osql utility
UPDATE
Red Gate now have a tool called SQL Multi Script, which basically does exactly what you want. I supports SQL 2000 to 2008 R2 and running queries on multiple databases in parallel which improve performance.
7 年后，我多次遇到同样的问题，所以我制作并发布了该项目：
TAKODEPLOY
这里有一些功能：
您可以在以下位置获取它：https://github.com/andreujuanc/TakoDeploy
7 years later i had the same issue so many times so I made it and published the project:
TAKODEPLOY
Here are some features:
You can get it at: https://github.com/andreujuanc/TakoDeploy
不确定这是否有效，但尝试用分号替换 GO 语句，并批量运行整个语句。
Not sure if this will work, but try replacing the GO statements with semicolons, and running the entire statement in one batch.
如果我记得的话，您还可以在 SQL Compare 中创建一个脚本，将所有内容更改回其开始时的状态。您可能想要生成这两个脚本。
当我进行这种部署时（已经有一段时间了），我首先加载到一个与 prod 完全相同的临时服务器，然后才开始确保脚本可以在 prod 上运行。如果出现任何失败（通常是由于脚本运行的顺序，例如无法为尚不存在的表设置外键）。我还首先编写了所有表更改的脚本，然后是所有视图更改，然后是所有 UDF 更改，然后是所有存储过程更改。这大大减少了由于对象尚不存在而导致的失败，但我通常仍然有一些需要调整。
If I recall, you can also create a script in SQL Compare to change everything back to the state it started in. You might want to generate both.
When I did this sort of deployment (it's been awhile), I first loaded to a staging server that was made exactly like prod before I started to make sure the scripts would work on prod. If anything failed (usually because of the order that scripts were run, can't set a foreign key to a table that doesn't exist yet for instance). I also scripted al table changes first, then all view changes, then all UDF changes, then all stored proc changes. This cut down greatly onthe failures due to objects not yet existing, but I still usually had a few that needed to be adjusted.