BLOG

3 Ways: How to Recover Deleted Stored Procedure from SQL Server

In database management, deleting important data or objects by mistake is a common problem. Among them, stored procedure, as an important feature in SQL Server, its accidental deletion may affect the normal operation of the system. However, there is no need to be too anxious because there are some methods that can help us of how to recover deleted stored procedure from SQL Server.

What is Stored Procedure

Stored procedure is a collection of pre-compiled SQL statements that users can execute as a single unit. It allows programmers to encapsulate complex business logic in the database, thereby improving code reusability, security, and performance.

In SQL Server, a Stored Procedure is a collection of pre-compiled T-SQL statements that are encapsulated together and given a name for easy invocation. Stored Procedures can take input parameters, execute complex business logic, and return a result set. Their main benefits include increased code reuse, enhanced security, reduced network traffic, and improved performance.

Causes of Deleted Stored Procedure

Also, before we learn about how to recover deleted stored procedure from SQL Server, we’d better know about the reasons for the accidental deletion of stored procedures. Deletion of stored procedure may occur due to a variety of reasons, including but not limited to the following:

  • Misuse: When writing or modifying a stored procedure, it may be deleted by mistake.
  • Database maintenance: When performing database maintenance or upgrading, some stored procedures that are no longer needed may be deleted.
  • Security policy: Some security policies may require periodic cleanup of stored procedures that are no longer needed.

How to Recover Deleted Stored Procedure from SQL Server through SMSS

SQL Server Management Studio (SMSS) is a graphical tool provided by Microsoft for managing SQL Server. The following are the steps on how to recover deleted stored procedure using SMSS:

  1. Open SMSS and connect to your SQL Server.
  2. Right click on “Programmability” -> “Stored Procedures”, and then select “Script Stored Procedure as” -> “CREATE To” -> “New Query Editor Window”.
  3. In the new query window, modify the CREATE PROCEDURE statement to the name and definition of the stored procedure you deleted by mistake.
  4. Finally, run the modified CREATE PROCEDURE statement to recreate the deleted stored procedure.

Recover Deleted Stored Procedure with MTM Database Recovery

If the above methods fail to recover the stored procedure, or you don’t have a backup, then you can consider using a third-party tool, such as MTM Database Recovery.

1. Download and install MTM Database Recovery.

Download and Install MTM Database Recovery for SQL Server

2. Open MTM Database Recovery, click the “Recovery” icon on the top and select the corresponding database file. Check “Recover deleted data” and then press “Recover” button to recover the stored procedure.

Select Deleted Stored Procedure in SQL File to Restore

3. In the tree view on the left, find and select the stored procedure to be recovered. On the right side of the details view, you can see the details of the stored procedure.

Preview and Select the Deleted Stored Procedure

4. Click the “Export” button on the top of the software and wait for the recovery to complete.

How to Quickly Recover Single Deleted Stored Procedure

If you only need to recover a single stored procedure, you can try the following steps for quick recovery:

Step 1: Backup Database

Before performing any recovery operation, it is always recommended to backup the database to prevent data loss. A full backup of the database can be created using the following command:

sql

BACKUP DATABASE YourDatabaseName TO DISK = ‘C:\Backup\YourDatabaseName.bak’

Step 2: Get the procedure definition

To restore a stored procedure, you first need to get the definition of the procedure. You can use the following query to get the text of the stored procedure:

sql

SELECT [text] FROM syscomments c

INNER JOIN sys.syscomments sc ON c.id = sc.id

WHERE sc.type = ‘P’ AND sc.number = (SELECT number FROM sys.syscomments sc2 WHERE sc2.type = ‘P’ AND sc2.id = OBJECT_ID(‘YourStoredProcedureName’))

Replace YourStoredProcedureName with the name of the procedure to be recovered. This query will return the definition text of the procedure.

Step 3: Create a New Procedure

Using the definition text in the query result, you can create a new procedure. In SQL Server Management Studio (SSMS) or any other SQL editor, execute the following T-SQL code:

sql

CREATE PROCEDURE YourStoredProcedureName @param1 datatype1, @param2 datatype2…

AS

BEGIN

— Paste the procedure definition text here.

END

Replace YourStoredProcedureName with the name of the procedure to be recovered and paste the definition text of the procedure between BEGIN and END after AS. Make sure that the definition text matches the original procedure exactly, including the parameter and statement order.

Step 4: Verify that the procedure was created successfully

You can execute the following query to verify that the procedure was created successfully:

sql

SELECT * FROM sys.procedures WHERE name = ‘YourStoredProcedureName’

If the query returns a result, the procedure was created successfully. At this point, you can try executing the procedure to ensure that it is working properly.

Conclusion

We can learn about how to recover deleted stored procedures from SQL Server 2014 or other versions, either through SMSS or MTM Database Recovery. However, remember that prevention is always the best strategy. Regular backups of your database and stored procedures can help you avoid the risk of data loss. At the same time, when using any tool for database recovery, please make sure to take care of the safety and accuracy of your operations. If you are unsure of your capabilities, seek professional help.

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注