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:
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:
- Using SQL Server Management Studio
- 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
- 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
- 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)
- SELECT * FROM sys.fn_dblog (NULL, NULL)
- WHERE [transaction name] IN ('DROPOBJ');
- Using a third-party software: To easily recover your MS SQL Database objects, Stellar Phoenix SQL Database Repair 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.
