Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
1  
Do you have a backup of your database? – Aaron Bertrand Oct 8 '12 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? – Jamshid Asatillayev Oct 8 '12 at 14:53
3  
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 Oct 8 '12 at 15:02

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
+1 for "make sure you have a source control". – a_horse_with_no_name Oct 8 '12 at 21:45
up vote 0 down vote

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...)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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