I have updated my database with this query:
我用这个查询更新了我的数据库:
UPDATE suppliers SET contactname = 'Dirk Lucky'
So all the rows in the suppliers table have the contact name: "Dirk Lucky."
因此,供应商表中的所有行都有联系人姓名:“Dirk Lucky”。
How can I rollback this transaction? I want to restore my the contactname column in the suppliers table.
我该如何回滚此交易?我想恢复供应商表中的contactname列。
Thanks, Programmer
3 个解决方案
#1
Sounds like your transaction has already been committed. You can't roll it back anymore.
听起来你的交易已经提交了。你再也不能回滚了。
Your only option is restoring a backup. You might be able to restore a backup as a new database, so you can copy only the contactnames and not lose any other changes.
您唯一的选择是恢复备份。您可以将备份还原为新数据库,因此您只能复制联系人名称而不会丢失任何其他更改。
#2
If you have the full log in FULL mode, you can do a restore from the log. There's an excellent blog post about it here.
如果您具有FULL模式的完整日志,则可以从日志中进行还原。这里有一篇很棒的博客文章。
If you don't, I seriously hope that you have a backup.
如果你不这样做,我真的希望你有一个备份。
For future reference: When I do updates, I use the following syntax:
供将来参考:当我进行更新时,我使用以下语法:
SELECT *
--UPDATE a SET col = 'val'
FROM myTable a
WHERE id = 1234
That way, you can see what you're selecting to update first. Then, when you're finally ready to update, you just select from UPDATE down and run the query. I've caught myself many times with this trick. It also works with deletes, so that's a bonus.
这样,您可以看到您首先要更新的内容。然后,当您最终准备好更新时,您只需从UPDATE中选择并运行查询。我用这个技巧多次抓住自己。它也适用于删除,所以这是一个奖金。
#3
Hopefully your database is using the Full Recovery Model. If so:
希望您的数据库使用完整恢复模型。如果是这样:
First you need to take a transaction log backup now.
首先,您需要立即进行事务日志备份。
You can then look to perform a database restore from your FULL backup + Differntials.
然后,您可以从FULL backup + Differntials执行数据库还原。
Once done, you can then restore the transaction log to a specific point in time prior to your update statement.
完成后,您可以将事务日志还原到更新语句之前的特定时间点。
See "How to Restore to a point in time"
请参阅“如何还原到某个时间点”
As other posters have suggested, you could restore to another database, and apply an update back to the live database should you wish to minimise downtime.
正如其他海报所建议的那样,您可以恢复到另一个数据库,并在您希望最大限度地减少停机时间时将更新应用回实时数据库。
Hope this makes sense but let me know if you need assistance.
希望这是有道理的,但如果您需要帮助,请告诉我。
#1
Sounds like your transaction has already been committed. You can't roll it back anymore.
听起来你的交易已经提交了。你再也不能回滚了。
Your only option is restoring a backup. You might be able to restore a backup as a new database, so you can copy only the contactnames and not lose any other changes.
您唯一的选择是恢复备份。您可以将备份还原为新数据库,因此您只能复制联系人名称而不会丢失任何其他更改。
#2
If you have the full log in FULL mode, you can do a restore from the log. There's an excellent blog post about it here.
如果您具有FULL模式的完整日志,则可以从日志中进行还原。这里有一篇很棒的博客文章。
If you don't, I seriously hope that you have a backup.
如果你不这样做,我真的希望你有一个备份。
For future reference: When I do updates, I use the following syntax:
供将来参考:当我进行更新时,我使用以下语法:
SELECT *
--UPDATE a SET col = 'val'
FROM myTable a
WHERE id = 1234
That way, you can see what you're selecting to update first. Then, when you're finally ready to update, you just select from UPDATE down and run the query. I've caught myself many times with this trick. It also works with deletes, so that's a bonus.
这样,您可以看到您首先要更新的内容。然后,当您最终准备好更新时,您只需从UPDATE中选择并运行查询。我用这个技巧多次抓住自己。它也适用于删除,所以这是一个奖金。
#3
Hopefully your database is using the Full Recovery Model. If so:
希望您的数据库使用完整恢复模型。如果是这样:
First you need to take a transaction log backup now.
首先,您需要立即进行事务日志备份。
You can then look to perform a database restore from your FULL backup + Differntials.
然后,您可以从FULL backup + Differntials执行数据库还原。
Once done, you can then restore the transaction log to a specific point in time prior to your update statement.
完成后,您可以将事务日志还原到更新语句之前的特定时间点。
See "How to Restore to a point in time"
请参阅“如何还原到某个时间点”
As other posters have suggested, you could restore to another database, and apply an update back to the live database should you wish to minimise downtime.
正如其他海报所建议的那样,您可以恢复到另一个数据库,并在您希望最大限度地减少停机时间时将更新应用回实时数据库。
Hope this makes sense but let me know if you need assistance.
希望这是有道理的,但如果您需要帮助,请告诉我。