How to Retrieve Dropped Stored Procedure, Function, Table in SQL
How to Retrieve Dropped Stored Procedure, Function, Table in SQL: One of our Code Scratcher’s viewer have some query in SQL. So let’s quick look at it.
Code Scratcher’s viewer request
How to Retrieve Dropped Stored Procedure, Function, Table in SQL Server 2008
I have a database with Employee_DB and I created a stored procedure in it and later on I deleted it and now I want to retrieve that deleted stored procedure.
I am employee in company so that I don’t have administrative permissions
So today we are come to you with SQL topic about how to retrieve dropped stored procedure, function, table in SQL.
Incoming search terms
How to Retrieve Dropped Stored Procedure, Function, Table in SQL, How to recover views, stored procedures, functions, and table in SQL, retrieve the deleted table, stored procedure in sql server 2008.
In previous articles we explained Minimum and Maximum Text Length Validation, Password Validation in ASP.NET, Get or Set Cursor Position in a Textarea, Vertical Image Slider in WPF, WPF ComboBox with Image, SQL Query List Part 3, Insert, Update, Delete Data into Database using Stored Procedure.
Following are the steps to retrieve dropped stored procedure, function, table in SQL
Create Recover Stored Procedure
Create new stored procedure in you database and then execute it with suitable parameter.
CREATE PROCEDURE [dbo].[sp_Recover_Dropped_Objects]
DECLARE @Compatibility_Level INT
FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name
RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
Select [Database Name],Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script]
Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST'
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DROPOBJ'
And CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
And Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))<>0
Execute Stored Procedure
After creating stored procedure you need to execute it. Pass only three parameter “Database Name“, “Date From” and “Date To” as per created SP.
EXEC sp_Recover_Dropped_Objects 'Name_of_Database','2015/06/20','2015/06/24'