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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
The answer – as always – is maybe :)
If you use the 'FULL' recovery model, And you have earlier full database backup,
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,
I suggest you take Aaron Bertrand's advice for next time (there will always be a next time...)