SQL 撤消对存储过程的更改

undo changes to a stored procedure

提问by Raphra

I altered a stored procedure and unknowingly overwrote some changes that were made to it by another developer. Is there a way to undo the changes and get the old script back?

我更改了一个存储过程，并在不知不觉中覆盖了其他开发人员对其所做的一些更改。有没有办法撤消更改并取回旧脚本？

Unfortunately I do not have a backup of that database, so that option is ruled out.

不幸的是，我没有该数据库的备份，因此排除了该选项。

回答by Bohemian

The answer is YES, you canget it back, but it's not easy. All databases log everychange made to it. You need to:

答案是肯定的，你可以拿回来，但这并不容易。所有数据库都记录对它所做的每一次更改。你需要：

1. Shutdown the server (or at least put it into read-only mode)
2. Take a full back up of the server
3. Get a copy of all the db log files going back to before when the accident happened
4. Restore the back up onto another server
5. Using db admin tools, roll back through the log files until you "undo" the accident
6. Examine the restored code in the stored proc and code it back into your current version

1. 关闭服务器（或至少将其置于只读模式）
2. 对服务器进行完整备份
3. 获取事故发生前所有数据库日志文件的副本
4. 将备份恢复到另一台服务器上
5. 使用数据库管理工具，回滚日志文件，直到“撤消”事故
6. 检查存储过程中恢复的代码并将其编码回当前版本

And most importantly: GET YOUR STORED PROCEDURE CODE UNDER SOURCE CONTROL

最重要的是：在源控制下获取您存储的程序代码

Most people don't "get" this concept: You can only make changesto a database; you can't roll back the code version like you can with application code. To "roll back", you must make morechanges and drop/define your stored proc (or whatever).

大多数人没有“理解”这个概念：您只能对数据库进行更改；您无法像使用应用程序代码那样回滚代码版本。要“回滚”，您必须进行更多更改并删除/定义您的存储过程（或其他任何内容）。

Note to nitpickers: By "roll back" I do not mean "transaction roll back". I mean you've made your changes and decide one the server is back up that the change is no good.

挑剔者注意：“回滚”不是指“事务回滚”。我的意思是您已经进行了更改，并决定服务器备份该更改是不好的。

回答by Aaron Bertrand

In addition to the sound advice to either use a backup or recover from source control (and if you're doing neither of those things, you need to start), you could also consider getting SSMS Tools Packfrom @MladenPrajdic. His Management Studio add-in allows you to keep a running history of all the queries you've worked on or executed, so it is very easy to go back in time and see previous versions. While that doesn't help you if someone else worked on the last known good version, if your entire team is using it, anyone can go back and see any version that was executed. You can dictate where it is saved (to your own file system, a network share, or a database), and fine-tune how often auto-save kicks in. Really priceless functionality, especially if you're lazy about backups and/or source control (though again, I stress, you should be doing these things before you touch your production server again).

除了使用备份或从源代码管理恢复的合理建议（如果您没有做这些事情，则需要开始），您还可以考虑从@MladenPrajdic获取SSMS 工具包. 他的 Management Studio 插件允许您保留您处理过或执行过的所有查询的运行历史记录，因此很容易回到过去并查看以前的版本。如果其他人在上一个已知的良好版本上工作，这对您没有帮助，但如果您的整个团队都在使用它，那么任何人都可以返回并查看已执行的任何版本。您可以指定它的保存位置（到您自己的文件系统、网络共享或数据库），并微调自动保存启动的频率。真正无价的功能，尤其是当您对备份和/或源代码控制（不过，我再次强调，您应该在再次接触生产服务器之前进行这些操作）。

回答by ta.speot.is

You could look through the cached execution plans and try to find the one where your colleague made his changes and run the relevant parts again.

您可以查看缓存的执行计划并尝试找到您的同事进行更改的那个，然后再次运行相关部分。

EDIT

编辑

Although Bohemian looks to have a good answer if you've got the changes in the TL, thisis what I'm talking about. Review the SQL text for the plan.

尽管如果您在 TL 中进行了更改，Bohemian 看起来有一个很好的答案，但这就是我要说的。查看计划的 SQL 文本。

SELECT  cached.*,
               sqltext.*
         FROM  sys.dm_exec_cached_plans cached
  CROSS APPLY  sys.dm_exec_sql_text (cached.plan_handle) AS sqltext

But as squillman points out, there is no execution plan for DDL.

但正如squillman 指出的那样，DDL 没有执行计划。

回答by Michael Ames

"Is there a way to undo the changes and get the old script back?"

“有没有办法撤消更改并取回旧脚本？”

Short answer: Nope.

简短的回答：不。

:-(

:-(

回答by squillman

You won't be able to get it back from the database side of things. Your options at this point are pretty much limited to 1) recover from backup, 2) go to source control or 3) hope that someone else has a copy still up in an editor somewhere or saved to a file.

您将无法从数据库方面取回它。此时您的选择几乎仅限于 1) 从备份中恢复，2) 转到源代码管理或 3) 希望其他人在某个地方的编辑器中仍有副本或保存到文件中。

If neither of these are an option for you, then here's the obligatory "you should take regular backups and use source control"....

如果这些都不适合您，那么这是强制性的“您应该定期备份并使用源代码控制”....

回答by phipywr

I'm way late to the game on this but I did this same thing this morning and found I had forgot to save my script at some point in the past and needed to recover it. (It will be in source control after I get done fixing this!!!)

我已经迟到了，但今天早上我做了同样的事情，发现我在过去的某个时候忘记了保存我的脚本，需要恢复它。（在我完成修复后，它将在源代码管理中！！！）

Some people mentioned restoring from a backup but no one really mentioned how easy this is if you have a back up. Moreover, you aren't locked into rolling back the production database. I think this is key and assuming you have a back up I would say this is a much better alternative to what has been voted up to the best answer.

有些人提到从备份中恢复，但没有人真正提到如果有备份是多么容易。此外，您不会被限制回滚生产数据库。我认为这是关键，假设你有备份，我会说这是一个更好的选择，而不是被投票选出的最佳答案。

All you have to do is take your back up and restore it to a new database. Pull out the sp you are looking for and voila, you've recovered the missing code.

您所要做的就是备份并将其恢复到新数据库。拉出您正在寻找的 sp，瞧，您已经恢复了丢失的代码。

Don't forget to drop the newly created database after you've recovered the missing file.

恢复丢失的文件后，不要忘记删除新创建的数据库。

回答by Hyman Holmes

I had the same problem, and I don't have the confidence to go restoring from log files to another server. I was pretty distraught until I realised the solution was very simple...

我遇到了同样的问题，我没有信心从日志文件恢复到另一台服务器。我非常心烦意乱，直到我意识到解决方案非常简单......

Press Ctrl-Z over and over until I had undone my changes and the run the ALTER PROCEDURE again.

一遍又一遍地按 Ctrl-Z，直到我撤消更改并再次运行 ALTER PROCEDURE。

Admittedly I was pretty lucky that I still had it there to revert to but it really is the easiest fix. Probably a bit late now though.

诚然，我很幸运，我仍然可以恢复它，但这确实是最简单的修复方法。不过现在可能有点晚了。

回答by JameSQL

If you have scripted the stored procedure out from management studio object explorer this will work. Before expand and collapse the object explorer just scroll and point to the stored procedure you have opened. Script the stored procedure as create or alter to then you can get the previous version of the proc since the object explorer doesn't refreshed yet. This is always my life saver.

如果您已经从管理工作室对象资源管理器中编写了存储过程的脚本，这将起作用。在展开和折叠对象浏览器之前，只需滚动并指向您打开的存储过程。将存储过程编写为 create 或 alter to 然后您可以获得该过程的先前版本，因为对象资源管理器尚未刷新。这永远是我的救命稻草。