Methods to Recover SQL Database Object and Stored Procedure

A Database object is the database structure which is either used to store or reference data. Stored procedure is the collection of Structured Query Language (SQL) statements and is stored in the compiled form in the database. The stored procedure could be shared by more than one program in the SQL Server database. After exercising so many precautions such as database full recovery model, updated database backup, and log backup, the chances are that your data can be corrupt.

Recover SQL Database object: A full database backup contains all objects; however, it cannot restore the existing database. To fix the problem, check the following solutions:

1. Using SQL Server Management Studio

Follow the steps below:

• Restore the backup on the server (Try on Test server)
• In next step, User needs to create DDL script. Right click on the object which is present in SSMS object explorer.
• Go to Script Stored Procedure as->Create To->New Query Editor Window
• Run the script against the original database

2. Using cache: The recently executed object could be present in the cache so, there is a chance to recover it from the cache. Execute the following queries:

• Cached.refcounts
• Cached.usecounts
• Cached.objtype
• SQLText.dbid
• SQLText.objectid
• SQLText.text
• Query.query_plan

         From:

• sys.dm_exec_cached_plans Cached
• CROSS APPLY sys.dm_exec_sql_text(Cached.plan_handle)SQLText
• CROSS APPLY sys.dm_exec_query_plan(Cached.plan_handle)Query 

From the result, a user can select the object and copy the text column. The main advantage of this process is that the user would not require a full backup.

3. Using fn_dblog: fn_dblog () is an undocumented function which is used to view the transaction log record for the current database. This function accepts two parameters:

• LSN (Log Sequence Number) is the first parameter. The user can also write NULL at this place.
• Second parameter shows the ending of LSN. At this place, Null is also accepted by the SP.
• Execute the fn_dblog function to recover the object:
• SELECT * FROM sys.fn_dblog (NULL, NULL)

As a result, the user will get the complete database transaction log data. It is present in the 129 columns. To get more narrow result, execute the following;

• SELECT * FROM sys.fn_dblog (NULL, NULL)
• WHERE [transaction name] IN ('DROPOBJ');

It will be the untenable form. To recover the object, users need to be familiar with this format and characteristics.

4. Using a third-party software: To easily recover your MS SQL Database objects, Stellar Repair for MS SQL tool would serve the purpose. Know its salient features by simply downloading the trial version of the software, selecting the .MDF file and, clicking on the checkbox “Include Deleted Records” in the interface. Its preview section allows users to see the deleted objects, check, and recover them. To save your object, you need to register the software.

Conclusion: You may opt for any of the above methods to recover the SQL database object. However, as the first method requires a full database backup, users can opt for the second method which does not require a full database backup. I hope you find all the above methods useful. For queries, you can drop the comment.