0

I had a Table-Valued Function in SQL Server and someone from my colleagues has altered it unexpectedly. How can I restore previous version easily? Is there any way to find the previous version?

Improve this question
3
  • 2
    Do you have a backup of your database?
    – Aaron Bertrand
    Commented Oct 8, 2012 at 14:43
  • I didn't have the backup for the version which worked correctly. Is there any tool which shows changes from something like change logs?
    – Jama
    Commented Oct 8, 2012 at 14:53
  • 4
    You might be able to find the ALTER FUNCTION event in the default trace, but you probably won't still have the original version, so it won't help you. You can also capture these things with a DDL trigger, but you'd have to do that in advance. This is why we take, test & save backups (and also a strong case to consider source control). They're the things you don't think you need until someone screws you over.
    – Aaron Bertrand
    Commented Oct 8, 2012 at 15:02

2 Answers 2

Reset to default
5

The answer is unfortunately no. Without some form of source control or a functional back-up to go to you are kind of out of luck. As soon as you push execute on that alter statement you have changed that table/function/view/StoredProc. For future precautions you should probably have nightly back-ups make sure only the appropriate people have the user permissions required to make database changes. Also you want to make sure you have source control and are following a proper procedure for testing and deployment to make sure this does not happen in the future.

Improve this answer
0
0

The answer – as always – is maybe :)

If you use the 'FULL' recovery model, And you have earlier full database backup,
You have the option to perform a log backup, and then do a restore to a point in time – to the moment before the function was altered.

Restoring to a different database name and file names will allow you to extract the code you were looking for and apply it to your current database without losing other (later) changes.

If you don't use the 'FULL' recovery model, Or you can't use this method for some other reason,
Then I'm sorry, I don't know of any other way.

I suggest you take Aaron Bertrand's advice for next time (there will always be a next time...)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.