在 SQL 2005 数据库之间传输对象和数据
我想在两个服务器(开发盒和实时盒)之间传输对象(表、存储过程、数据等),并且想知道执行此操作的最佳方法是什么?
在SQL Server 2000中,您可以在数据库之间传输所有对象和数据。 现在只剩下“复制数据”和“编写查询”了。 第二个选项去哪儿了?
两个数据库都是 SQL 2005(带有 Service Pack 2)。 传输时,主键和关系以及所有视图和其他与 ASP.NET 身份验证相关的数据应保持完整。 集成服务未在实时服务器上设置,因此这不是一个选项。
我能想到的唯一方法是生成脚本,然后在其他服务器上运行它们,但这比旧方法更耗时(这就是我现在正在做的)。
I am wanting to transfer objects (tables, stored procedures, data etc) between two servers (Dev box and Live box) and was wondering what the best approach for doing this is?
In SQL Server 2000, you could transfer all objects and data between databases. Now all there is is 'copy data' and 'write a query'. Where has the second option gone?
Both databases are SQL 2005 (with service pack 2). When transferring, primary keys and relationships should be kept intact as well as all the views and other associated data with regards to ASP.NET authentication. Integration Services is not setup up on the live server, so that is not an option.
The only way I can think of is generating scripts, then running them on the other server, but that is more time consuming than the old way (this is how I am doing it now).
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
如果你愿意付费,我推荐Red Gate的Sql Compare和Sql Data Compare。
非常有用的产品。
If you are willing to pay, I recommend Sql Compare and Sql Data Compare from Red Gate.
Very useful products.
数据库发布向导
http://sqlhost.codeplex.com/
Database Publishing Wizard
http://sqlhost.codeplex.com/
遗憾的是您没有安装集成服务,因为您可以使用“复制数据库向导”。 我相信这会创建一个在目标服务器上运行的 SSIS 包。
如果您有 Visual Studio 2008,您可以尝试数据比较和架构比较工具。
It's a shame you haven't got Integration Services installed as you could use the "Copy Database Wizard". I believe this creates an SSIS package that runs on the destination server.
If you have Visual Studio 2008, you could try the Data comparison and Schema comparison tools.
你最好的选择可能是一个模式& 数据比较工具; http://www.mssqltips.com/tip.asp?tip 列出了各种工具=1069
Your best bet is probably a schema & data comparison tool; there's various tools listed at http://www.mssqltips.com/tip.asp?tip=1069
你没有提到你的应用程序的范围或开发人员的数量等,所以很难提出任何建议。 但是,如果您的开发由多个并发项目和多个开发人员组成,并且您正在从开发复制到生产,我会建议如下所示:
注意:质量保证与生产几乎相同,除了等待最终生产安装的应用更改。 dev 包含任何正在进行的更改、额外的调试垃圾等。您可以定期将生产备份恢复到 qa 和 dev 上以重新同步它们(只需确保所有开发人员都意识到这一点并做出相应的计划),因为(取决于随着时间的推移,他们(生产、质量保证、开发)将开始产生更多差异。
You don't mention the scope of your application or the number of developers, etc., so it is a little hard to make any recommendations. However, if your development consists of multiple concurrent projects and multiple developers and you are copying from a Development to Production I would recommend something like the following:
note: qa is almost identical to production, except applied changes waiting for their final production install. dev contains any work in progress changes, extra debug junk, etc. You can periodically restore a production backup onto qa and dev to resync them (just make sure all developers are aware of this and plan accordingly), because (depending on the number of developers) they (production vs. qa vs. dev) will start to incur more differences over time.