SQL Server wiped my table after (incorrectly) crea

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.

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".

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?

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.