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

psivaprasad · January 10, 2019 6:07PM

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 · January 15, 2019 11:59AM

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 · March 18, 2019 10:43AM

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 · July 18, 2019 10:09AM

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 · August 26, 2019 6:16PM

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

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

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk