Is there a way to restore a previous version of a stored procedure in SQL Server?

psivaprasad

I frequently use the right-click / Modify option in SQL Server Management Studio to pull up existing code in stored procedures in order to reuse the code. Only, this time I did that but forgot to remove the ALTER PROC piece of the code and modified an existing procedure.

Is there a way to restore the last version of a stored procedure without restoring the entire database? I have a saved .sql file that I can use to restore it, but I'm not sure how old that file is compared to the most recent version of the procedure.

Also, I'm not a DBA, more of a Jr-ish SQL Server DBA Developer. And our DBA is out for a couple weeks

I'm looking for a answer to my question,Any help here is appreciated! Thank you.

squigley

Are you by chance using SQL Source Control? If so, and it's linked to a repo that supports this, you can use View History to get a specific version of the proc - https://documentation.red-gate.com/soc7/common-tasks/view-source-control-history 

Alternatively, you could use SQL Compare to compare the backup of the previous version to the live database, and then deploy just that proc.

amalia_tracy

There is no way to extract data directly from the backup file. So if you only have a backup copy of your database, you will need to find an older version of SQL Server or get installation disks to set up that version of SQL Server to restore the data.

LenaFischmann

Restore it from the backup of the database.To avoid such problems in future try to implement this  https://docsbay.net/sql-server-ddl-triggers-to-track-all-database-changes . It is a short term fix if you don't have super duper source control system's. 

CT_CRAIG

Another question would be are you using Tab History.  I've found that it has saved my bacon many times