Retrieve previous version of a stored procedure

Question

I have modified a stored procedure today but after that I realized that it is wrong. So I want revert it back. Is there any way to get the previously modified stored procedure or else last date's stored procedure.I don't have any back up also. Thank you.

Solution 1

Not directly within SQL Server. Unless you have kept a script in source control or elsewhere your only option is to restore an older backup to another environment and script out the old version from that.

Solution 2

No, unless the SSMS query window you used to alter the procedure still has the old text in its undo buffer. You must restore a backup copy and manually transfer the procedure from database copy. You shouldn't be updating the database directly, but use a version controlled script instead to start with.

In future you can also install tools like the SSMS Toolpack that keep a history of every query you've run.

Solution 3

Have you any backups of the database itself? The stored procedure is part of the database. Restore it onto a spare machine and extract it from there.

If you don't have a backup of the database... huh???

Comments

[ConcernedOfTunbridgeWells]

You're pretty much buggered then. If that is the case you will have to reconstruct the previous version by hand.