将数据库模式复制到现有数据库 [英] Copy database schema to an existing database

问题描述

我使用Microsoft Sql Server Management Studio。
我目前有一个现有的数据库中的数据，我将调用DatabaseProd
我有一个第二个数据库，用于测试数据，所以数据是不完全正确，也不是最新的。我将把这个数据库称为DatabaseDev。

I'm using Microsoft Sql Server Management Studio. I currently have an existing database with data in it, which I will call DatabaseProd And I have a second database with data used for testing, so the data isn't exactly correct nor up to date. I will call this database DatabaseDev.

但是DatabaseDev现在包含新添加的表和新添加的列等。

However DatabaseDev now contains newly added tables and newly added columns,etc etc.

我想将这个新模式从DatabaseDev复制到DatabaseProd，同时保留DatabaseProd的数据。

I would like to copy this new schema from DatabaseDev to DatabaseProd while keeping the DatabaseProd's Data.

DatabaseProd包含2个表
TableA与列ID和名称
TableB与列ID和jobName
，这些表包含我想保留的数据

Ex. DatabaseProd contains 2 tables TableA with column ID and Name TableB with column ID and jobName and these tables contains data that I would like to keep

DatabaseDev包含3个表
TableA与列ID，名称和phoneNum
TableB与列ID和jobName
TableC与列ID和文档
和这些表包含我不需要的数据

DatabaseDev contains 3 tables TableA with column ID ,Name and phoneNum TableB with column ID and jobName TableC with column ID and document and these tables contains Data that I dont need

将DatabaseDev模式复制到DatabaseProd，但保留来自DatabaseProd的数据
因此，复制之后的DatabaseProd将如下所示
TableA与列ID，名称和phoneNum
TableB与列ID和jobName
TableC与列ID和文档
但是表将包含它的原始数据。

Copy DatabaseDev Schema to DatabaseProd but keep the data from DatabaseProd So DatabaseProd after the copy would look like this TableA with column ID ,Name and phoneNum TableB with column ID and jobName TableC with column ID and document But the tables would contain it's original Data.

这是可能吗？

谢谢

推荐答案

p>您可以使用 Red-Gate SQL比较，此将允许您比较两个数据库并生成一个脚本在源数据库上运行。您必须支付许可证，但您将获得 14天试用期

You can use Red-Gate SQL Compare, this will allow you to compare both DB's and generate a script to run on the source DB. You have to pay for a license, but you will get a 14-day trial period.

此工具，以及数据比较和两个我始终坚持的工具

This tool, along with Data Compare and two tools I always insist on with new roles as they speed up development time, and minimise human error.

此外，使用SQL比较时，一个好的提示 - 如果你需要生成一个回滚脚本，然后可以编辑项目（在创建 rollout脚本后），切换源和目标周围，这将创建一个脚本，如果 rollout脚本失败，它会将模式返回到其原始状态。但是，在执行此操作时非常小心，并且不要选择与sql compare 同步，而应生成一个脚本查看图片。我无法上传图片，但我已在此处连结了 - 您可以看到两个选项，选取使用SQL比较产生指令码/同步处理。

Also, a good tip when using SQL compare - if you need to generate a rollback script, then you can edit the project (after creating your rollout script), switch the source and destination around and this will create a script which will return the schema back to it's original state if the rollout script fails. However, be very careful when doing this, and don't select synchronize with sql compare, rather generate a script, see image. I can't upload an image, but I have linked to one here - you can see the two options to select Generate Script / Sync using SQL compare.

这篇关于将数据库模式复制到现有数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！