SQL Server 在(错误地)创建一个新列后擦除了我的表……到底发生了什么? [英] SQL Server wiped my table after (incorrectly) creating a new column .. what the heck happened?

问题描述

我在 SQL Server Management Studio 表设计器的现有表中添加了一个新列.输入 INT，不为空.未设置默认值.

I added a new column to an existing table in the SQL Server Management Studio table designer. Type INT, not null. Didn't set a default value.

我生成了一个更改脚本并运行它，它出错了，警告说新列不允许空值，并且没有设置默认值.它说0 行受影响".

I generated a change script and ran it, it errored out with a warning that the new column does not allow nulls, and no default value was being set. It said "0 rows affected".

数据仍然存在，并且由于某种原因，我的新列在 SSMS 左侧数据库树的列"文件夹中可见，即使它显示0 行受影响"并且未能更改数据库.

Data was still there, and for some reason my new column was visible in the "columns" folder on the database tree on the left of SSMS even though it said "0 rows affected" and failed to make the database change.

因为新列在列表中可见，我想我会继续更新所有行并添加一个值.

Because the new column was visible in the list, I thought I would go ahead and update all rows and add a value in.

UPDATE MyTable SET NewColumn = 0

繁荣.. 桌子擦干净了.删除了每一行.

Boom.. table wiped clean. Every row deleted.

这是一个大问题，因为它位于我不知道的没有备份的生产数据库上.但是..可以通过一些手动输入恢复，所以不是世界末日.

This is a big problem because it was on a production database that wasn't being backed up unbeknownst to me. But.. recoverable with some manual entry, so not the end of the world.

有谁知道这里可能发生了什么......也许内部发生了什么可能导致我的更新语句清除表中的每一行?

Anyone know what could have happened here.. and maybe what was going on internally that could have caused my update statement to wipe out every row in the table?

推荐答案

UPDATE 语句不能删除行，除非有触发器在之后执行删除，而你说表没有触发器.

An UPDATE statement can't delete rows unless there is a trigger that performs the delete afterward, and you say the table has no triggers.

所以它必须是我在评论中为您设计的场景:行没有正确加载到新表，旧表被删除.

So it had to be the scenario I laid out for you in my comment: The rows did not get loaded properly to the new table, and the old table was dropped.

请注意，它甚至有可能看起来适合您，在某一时刻确实加载了行——如果事务未提交，然后(例如)稍后当您的会话终止时，事务会自动回滚.事务也可能因其他原因回滚.

Note that it is even possible for it to have looked right for you, where the rows did get loaded at one point--if the transaction was not committed, and then (for example) later when your session was terminated the transaction was automatically rolled back. The transaction could have been rolled back for other reasons, too.

此外，我的顺序可能不正确:它可能会以新名称创建新表、加载行、删除旧表并重命名新表.在这种情况下，您可能一直在查询错误的表以查明数据是否已加载.我现在一时想不起来表设计者是如何构建脚本的——给这只猫剥皮的方法不止一种.

Also, I may have gotten the order incorrect: it may create the new table under a new name, load the rows, drop the old table, and rename the new one. In this case, you may have been querying the wrong table to find out if the data had been loaded. I can't remember off the top of my head right now which way the table designer structures its scripts--there's more than one way to skin this cat.

这篇关于SQL Server 在(错误地)创建一个新列后擦除了我的表……到底发生了什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！