创建数据库副本 - 由于使用“身份”而失败；规格
我一直在试图弄清楚在生成重新创建数据库的脚本时如何正确适应如何处理“身份”列。
我需要为此生成脚本的最初原因是因为我必须将 SQL 数据库“降级”到旧版本。我知道数据库（v10.5）中的所有内容都与旧版本（v10.0）兼容。我面临的问题是，在复制数据库的 3 种不同方法中，它总是失败，因为它无法维护原始 ID 字段（即身份）。
我的每个表都有第一列 ID: Int = PK &身份。我也有很多情况，表在该列中没有完全按顺序排列，例如 1、2、3、5、8、12、13 等。这仅仅是因为这些记录过去已被删除。但似乎不可能按照与以前相同的顺序重新插入原始 ID 号...
那么如何将整个数据库从服务器 A 复制（无需备份/恢复）到服务器 B？注意：我可以从 Management Studio 连接到两台服务器上的两个数据库。另外，目标服务器不是我的，它是一个共享托管数据库，我只能访问我的数据库。我无权更改目标服务器设置。
我尝试了以下操作：
- 为整个数据库选项生成脚本
- 导出数据库选项
- 备份/恢复数据库 - 由于版本不匹配而失败
我猜我可能只需要暂时“禁用”所有表上的身份规范，插入数据，然后再次打开身份。但是我不擅长编写用于操作数据库结构的脚本。数据本身，我能做到。但是在操作数据库结构方面，我已经习惯了使用工具来完成此任务，以至于我什至从未花时间使用脚本 - 除了这个特定的场景之外，希望我永远不必学习。
I've been trying to figure out how to properly accommodate how to handle 'Identity' columns when generating a script to re-create the database.
The original reason why I need to generate a script for this is because I have to 'downgrade' a SQL database to an older version. I know everything in the database (v10.5) is compatible with the older version (v10.0). The issue I'm facing is that out of 3 different methods of copying the database, it always fails with the fact that it cannot maintain the original ID fields (which are identity).
Every table of mine has the very first column ID: Int = PK & Identity. I also have many cases where a table doesn't perfectly go sequential in this column, for example, 1, 2, 3, 5, 8, 12, 13, etc. That is simply because those records had been deleted in the past. But it seems as if it's impossible to re-insert the original ID numbers in the same order as they used to be...
So how do I copy (without backup/restore) a database in its entirety from Server A to Server B? NOTE: I can connect to both databases on both servers from the Management Studio. Also, the destination server is not mine, it is a shared hosted DB and I have access only to my database. I have no authority to change destination server settings.
I've tried the following:
- Generate script for entire database option
- Export database option
- Backup/Restore database - fails because of version mis-match
I'm guessing that I may just have to temporarily 'disable' the identity specification on all the tables, insert the data, then switch identity back on again. But I am horrible with writing scripts for manipulating the database structure. Data its self, I can do. But manipulating the database structure, I've gotten so used to using tools for this that I've never even taken the time to work with the scripts - and other than this particular scenario, hope that I never have to learn either.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我其实已经想通了。我已经知道必须有一种方法可以暂时禁用身份规范，但解决方案有点不同。除了“禁用”和“重新启用”身份规范之外，还有另一个名为 IDENTITY_INSERT 的命令（如上面的注释中所示），打开该命令后，它允许将值插入到身份字段 - 并且您需要确保它也被关闭。
IDENTITY_INSERT开关是针对每个连接会话的，因此它不会影响其他会话。只要IDENTITY_INSERT处于启用状态，您就可以插入具有该标识字段的特定值的记录 - 只要它仍在主键约束内。实际的解决方案不是使用
SET IDENTITY_INSERT MyTableName ON编写脚本，而是在数据库导出实用程序（在 SQL Management Studio 中）中，选择表时，选择所有表并选择高级设置使用IDENTITY_INSERT。I actually figured it out. I already knew that there must be a way to temporarily disable the identity specification, but the solution was a little different. Instead of 'disabling' and 're-enabling' the identity specification, there's another command (as in a comment above) called
IDENTITY_INSERTwhich when switched on, it allows inserting values to an identity field - and you need to ensure it gets switched back off too. TheIDENTITY_INSERTswitch is per-connection-session, so it does not affect other sessions. As long asIDENTITY_INSERTis on, you can insert records with a specific value for that identity field - so long as it's still within the primary key constraints.The actual solution was NOT to write a script with
SET IDENTITY_INSERT MyTableName ON, but rather in the database export utility (in SQL management studio), when selecting the tables, select all the tables and choose the advanced setting to useIDENTITY_INSERT.