SQL Server - How to retrieve source code for deleted objects (View, Stored Procedure, Function, and Trigger)

Views: 1.650
Reading Time: 7 minutes

Hey guys!
All right ?

In this article I will share with you a life-saving solution in desperation when someone deletes a database object (View, Stored Procedure, View or Function), you don't have a database object versioning routine and you You need to try to recover this object quickly without having to back it up (which may take a while, depending on the size of the base).

If you want to avoid this kind of situation (which is not always possible to retrieve object code), I suggest implementing a version control for database objects, made available in the post. How to create an Audit trigger to log object manipulation in SQL Server.

If you just need to know if a specific object has been deleted and who has deleted it, you can use standard SQL Server Trace (which is enabled by default) to get this information. Learn more in the post Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable.

When you have no version control over your database objects (View, Stored Procedure, Function, and Trigger) and someone accidentally deletes this object, a big problem may have been created for the DBA, especially when this object does not exist or It is not updated in other environments (DEV, QA, etc.), as you will need to upload a parallel base with the last backup to retrieve the source code for this object, which can take a lot of time and effort.

The purpose of this article is to provide some alternatives that can help you recover the source of these objects, even without version control (which is not ideal) and without having to back up.

Test environment

For testing this article, I will create a new database, set Recovery model to FULL, and create some objects. After that, I will use these objects and we will try to retrieve them again.

Script used to create the bank:

Script used to create objects:

Now, I will delete the objects:

How to recover deleted objects by reading T-log

One of the alternatives for retrieving the source code of deleted objects is to read transactions and data from the Transaction Log from SQL Server, in a tip I received from MVP Rafael Santos in a Whatsapp group.

Unbeknownst to you, Transaction Log is a critical database component that stores all changes made to a database, both in structure and data. When the CHECKPOINT command is executed (either manually or automatically), Transaction-Log data is stored in the bank data file and is removed from the transaction log. To better understand how the CHECKPOINT process works, read the excellent post. The CHECKPOINT Process, by Vitor Fava.

That said, it is already clear that the lifetime of the data stored in Transaction Log is limited and this leaves a little to be desired in the reliability that will be possible to recover the desired data. Also, in order to be able to retrieve transaction log data, the base Recovery Model must be set to FULL.

That said, we will try to recover the deleted objects at the beginning of this post. To do this, we will use the undocumented sys.fn_dblog function in the Stored Procedure below:

Example of use:

As you can see, we can easily retrieve the code from the 3 objects we deleted. This was only possible because the recovery model was set to FULL and the data was still stored in the Transaction Log.

In environments that have frequent log backups (I suppose most), the time you have between deleting data and trying to recover it can be quite short, as when logging back and / or running a CHECKPOINT , this data is no longer accessible using this function.

How to recover deleted objects by reading log backups

Using a slightly different solution than before, I will demonstrate how to recover deleted objects by reading log backups, which is when you back up the transaction log and the data that was available using the undocumented sys.fn_dblog function is no longer more accessible since transaction log data is stored in log backup files and the transaction log is truncated.

To get around this, Microsoft provides us with the fn_dump_dblog function, where we can read log backup files and extract information regarding transactions performed on the instance.

To do this, I will delete the created objects, perform a full base backup, recreate the objects, delete them again and perform a log backup:

Now, let's read the log backup data:

Result:

As you can see from above, as I backed up the log, stpRecupera_Object_Excluded (sys.fn_dblog) no longer returned the create object commands we want to retrieve.

However, by using stpRecupera_Object_Excluidos_Backup (sys.fn_dump_dblog) and specifying the log backup file we created, it was possible to retrieve the object creation commands.

Using ApexSQL Log

Another way to retrieve the source code of deleted objects and even view modified / deleted data in tables is by using the paid tool. ApexSQL Log. This unique feature is easy to use, intuitive and allows you to analyze Transaction Log data and can also retrieve information from Transaction-Log backups.

Operations subject to recovery:

ApexSQL Log Demo:

How to retrieve queries by reading the plan cache

Another alternative to trying to retrieve object and query code is by querying the execution plan cache. When a query or object is executed, SQL Server analyzes whether it will cache that execution (for performance purposes) or not.

Although not very applicable to the scenario proposed in this post (since when altering / deleting an object, its caching is usually deleted as well), the query below may be useful for retrieving queries previously performed:

Result:

It is noteworthy that the lifetime of a cached execution is very limited and short, where the cache can be cleared as queries are executed or even if the SQL service is restarted.

How to retrieve queries using SQL Prompt

As I had already shown in the post SQL Server - Write T-SQL as a Ninja Using Redgate SQL PromptSQL Prompt has a very nice feature called “Tab History” which stores all queries or objects you have opened since you installed SQL Prompt.

If you recently opened / changed this deleted object, chances are you can recover it using Tab History.

This feature is really very practical, useful and sometimes can even save lives .. lol .. In my previous company, my Tab History had almost 2 GB, with all the queries / objects I had already opened / viewed. code in the last 4 years (and has saved me a LOT of time).

Well, that's it, guys!
I hope you enjoyed this post.

A hug and see you next.