从生产中恢复开发数据库：基于表中存储的列表运行一组 SQL 脚本？
我需要从生产数据库恢复备份，然后自动重新应用 SQL 脚本（例如 ALTER TABLE、INSERT 等）以将该数据库模式恢复到正在开发的状态。
会有很多脚本，来自少数不同的开发人员。它们不会全部位于同一目录中。
我当前的计划是在伪系统数据库的表中列出具有完整文件系统路径的脚本。然后在此数据库中创建一个存储过程，该过程将首先运行 RESTORE DATABASE，然后在脚本列表上运行光标，为每个脚本创建 SQLCMD 命令字符串，然后使用 xp_cmdshell 为每个脚本执行该 SQLCMD 字符串。
光标 - > sqlstring - > xp_cmdshell - > sqlcmd 的顺序对我来说感觉很笨拙。另外，它需要打开 xp_cmdshell。
我不可能是唯一一个做过这样事的人。是否有一种更干净的方法来运行一组分散在服务器上文件系统中的脚本？特别是，一种不需要 xp_cmdshell 的方法？
I need to restore a backup from a production database and then automatically reapply SQL scripts (e.g. ALTER TABLE, INSERT, etc) to bring that db schema back to what was under development.
There will be lots of scripts, from a handful of different developers. They won't all be in the same directory.
My current plan is to list the scripts with the full filesystem path in table in a psuedo-system database. Then create a stored procedure in this database which will first run RESTORE DATABASE and then run a cursor over the list of scripts, creating a command string for SQLCMD for each script, and then executing that SQLCMD string for each script using xp_cmdshell.
The sequence of cursor->sqlstring->xp_cmdshell->sqlcmd feels clumsy to me. Also, it requires turning on xp_cmdshell.
I can't be the only one who has done something like this. Is there a cleaner way to run a set of scripts that are scattered around the filesystem on the server? Especially, a way that doesn't require xp_cmdshell?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，第一点是将所有数据库脚本收集到一个中心位置。某种形式的源代码控制或版本控制是最好的，因为您可以看到谁修改了什么、何时修改以及（如果没有别的办法，使用 diff 工具）修改的原因。将用于创建数据库的代码留在网络各处可能会导致灾难。
其次，您需要针对数据库运行脚本。这意味着您需要某人或某物来运行它们，这意味着执行代码。如果您从 SQL Server 内部执行此代码，您几乎最终将使用 xp_cmdshell。另一种选择？使用可以针对数据库运行脚本的其他东西。
我当前对此类问题的解决方案是将脚本存储在文本（.sql）文件中，将文件存储在源代码管理中，并仔细跟踪它们的执行顺序（例如，CREATE TABLEs get run在添加后续列的 ALTER TABLE 之前）。然后我有一个批处理文件——是的，我已经有一段时间了，你可以用大多数任何语言来执行此操作——调用 SQLCMD（我们使用的是 SQL 2005，我曾经使用 osql）并运行这些脚本针对必要的数据库。
如果您不想尝试“自己动手”，可能有更正式的工具可以帮助管理此过程。
First off and A-number-one, collect all the database scripts in one central location. Some form of Source Control or Version Control is best, as you can then see who modified what when and (using diff tools if nothing else) why. Leaving the code used to create your databases hither and yon about your network could be a recipe for disaster.
Second off, you need to run scripts against your database. That means you need someone or something to run them, which means executing code. If you're performing this code execution from within SQL Server, you pretty much are going to end up using xp_cmdshell. The alternative? Use something else that can run scripts against databases.
My current solution to this kind of problem is to store the scripts in text (.sql) files, store the files in source control, and keep careful track of the order in which they are to be executed (for example, CREATE TABLEs get run before ALTER TABLEs that add subsequent columns). I then have a batch file--yeah, I've been around for a while, you could do this in most any language--to call SQLCMD (we're on SQL 2005, I used to use osql) and run these scripts against the necessary database(s).
If you don't want to try and "roll your own", there may be more formal tools out there to help manage this process.
除了 Phillip Kelley 提出的有关集中化和源代码控制的建议之外，如果您熟悉 .NET，您可能会考虑编写一个使用 SQL Server SMO（SQL Server 管理对象）的小型 WinForms 或 WebForms 应用程序。有了它，您可以将整个脚本传递到数据库，就像将其放入 Management Studio 中一样。这避免了对 xp_cmdshell 和 sqlcmd 的需要。另一种选择是创建一个 DTS/SSIS 包，该包将读取文件并在循环中使用“执行 T-SQL”任务。
Beyond the suggestions about centralization and source control made by Phillip Kelley, if you are familiar with .NET, you might consider writing a small WinForms or WebForms app that uses the SQL Server SMO (SQL Server Management Objects). With it, you can pass an entire script to the database just as if you had droppped it into Management Studio. That avoids the need for xp_cmdshell and sqlcmd. Another option would be to create a DTS/SSIS package that would read the files and use the Execute T-SQL task in a loop.