生成与 Transact SQL 等效的脚本
在 SQL Server Management Studio 中,有一个“生成脚本”选项。我们当前将 2008 sql server 数据库传输到 2005 sql server 数据库的方法是生成脚本并在该 sql 脚本文件上运行 SQLCMD 以推送到 2005 sql server 的本地实例。我正在创建一个 C# 程序来完成一大堆其他任务,但我还需要它来转换 2008 数据库并将其移动到 2005 sql 服务器。是否有 Transact sql 语句或更简单的方法将 2008 数据库转换为 2005 数据库?谢谢。
In SQL Server Management Studio, there is an option to 'Generate Scripts'. The way we currently transfer a 2008 sql server database to a 2005 sql server database is by generating the scripts and running SQLCMD on that sql script file to push to a local instance of 2005 sql server. I'm creating a C# program to do whole bunch of other tasks as well but I also need it to convert the 2008 database and move it to a 2005 sql server. Is there a Transact sql statement or an easier way to convert the 2008 database to a 2005 database? Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您的开发交付成果不应该是数据库二进制文件(.MDF 文件),而应该是部署脚本。您像对待任何其他源文件一样对待数据库部署和升级,将其置于源代码控制之下,在签入时进行同行代码审查等。直接修改 .MDF,然后进行逆向工程来部署它是非常糟糕的。您现在遇到的问题只是其中之一,还有更多问题,特别是在应用程序版本升级期间完成的问题架构更改。请参阅版本控制和您的数据库。
现在确实是,整个 VS 工具集正在尝试引导您走上“只需在 VS 数据库资源管理器中编辑 MDF,一切都会好起来”的道路。一切都不会好起来,一次或多次部署崩溃就在你的生活中,但让我们假装 VS 做了一件好事。
您可以通过第 3 方商业工具(例如 Red Gate 的 SQL Compare,或者您可以相当轻松地推出自己的“生成脚本”。 SSMS 所做的一切都是调用 SMO 脚本编写功能来编写整个数据库的脚本。您可以执行相同的操作:实例化
Scripter
对象实例,然后向其中添加要编写脚本的对象,然后提取 T-SQL 生成的脚本。这正是 SSMS 中“生成脚本”的作用。 MSDN 中有一个脚本的示例:Your development deliverable should not be a database binary (the .MDF file), but a deployment script. You treat your database deployment and upgrade just like any other source file, place it under source control, have peer code reviews at check in etc etc. Modifying directly the .MDF and then reverse engineering to deploy it is just plain bad. The problem you encountered now is just one of the problems, and there are many more problems, specially around the issue schema changes done during an application version upgrade. See Version Control and your Database.
Now is true that the entire VS tool set is trying to guide you down the path of 'just edit your MDF in the VS Database Explorer and everything will be fine'. Nothing will be fine and one or more deployment meltdowns are just ahead in your life, but lets pretend that VS does a good thing.
You can automate the extraction of the current schema and deployment of it via 3rd party commercial tools like Red Gate's SQL Compare, or you can roll your own 'Generate Scripts' fairly easy. SSMS all it does it invokes the SMO scripting capabilities to script out an entire database. You can do the same: instantiate a
Scripter
object instance, then add to it the objects you want scripted, then extract the T-SQL generated script. That is exactly what 'Generate Scripts' in SSMS does. There is an example in MSDN for scripting:从一个数据库导入另一个数据库
Import from one DB in the other
正确的方法是编写数据库对象脚本,然后在旧实例上运行它们。如果您编码了 SQL 2008 特定的功能,那么当您在 2005 上运行脚本时,您会立即发现它(因此在生产中尝试之前先进行测试!)。
设置兼容模式在这里没有帮助。如果我有一个 (2008) 声明的表类型,并且将其与存储过程参数一起使用,则 SQL 或其他任何人都无法将其迁移到 2005。使用“现代”系统来支持遗留系统是充其量是丑陋的。
因为我要再次这样做,所以我首选的迁移路径是:
Scripting the database objects out and then running them on the older instance is the way to go. If you have SQL 2008-specific features coded, you'll find it right off when you run the script on 2005 (so test before you try it on Production!).
Setting Compatibility Mode will not help here. If I have a (2008) declared table type and I use it with stored procedure parameters, there's nothing SQL or anyone else can do to migrate it to 2005. Using "modern" systems to support legacy systems is ugly at best.
Just 'cause I'm doing it again, my preferred migration path is: