In my first article, I wrote about “How to recover the deleted data from SQL Server” from SQL server log. After this article, I received a lot of queries to recover dropped objects like tables, stored procedure, functions & triggers with the help of SQL server Log.
In this article, we will learn how to recover views, stored procedures, functions & triggers via SQL server log.
Step 1 :
Lets create few objects to explain the recovery process.
CREATE TABLE [dbo].[Student]( [Sno] [int] NOT NULL, [Student ID] nvarchar(6) Not NULL , [Student name] [varchar](50) NOT NULL, [Date of Birth] datetime not null, [Weight] [int] NULL) GO Create View Vw_Student as Select * from [Student] GO Create Procedure SP_Student @StudentID nvarchar(6) as Select * from Student Where [Student ID] =@StudentID GO Create FUNCTION [dbo].[Fn_Student](@StudentID nvarchar(6)) RETURNS int AS Begin Declare @Weight int Select @Weight = [Weight] from Student Where [Student ID] =@StudentID Return @Weight End GO CREATE TRIGGER trg_Student ON Student FOR INSERT AS RAISERROR (50009, 16, 10) GO
Step 2:
Lets drop these objects.
Drop View [dbo].[Vw_Student] GO Drop Procedure [dbo].SP_Student GO Drop Function [dbo].[Fn_Student] GO Drop Trigger [dbo].[trg_Student] GO
Step 3:
Check the existence of these objects to make sure that objects are dropped properly.
Select * from [Vw_Student] GO EXEC SP_Student 1 GO Select dbo.[Fn_Student](1)
Step 4:
Create the given below stored procedure to recover the dropped objects.
-- Script Name: Recover_Dropped_Objects_Proc -- Script Type : Recovery Procedure -- Develop By: Muhammad Imran -- Date Created: 04 Dec 2012 -- Modify Date: -- Version : 1.0 Create PROCEDURE Recover_Dropped_Objects_Proc @Database_Name NVARCHAR(MAX), @Date_From DATETIME='1900/01/01', @Date_To DATETIME ='9999/12/31' AS DECLARE @Compatibility_Level INT SELECT @Compatibility_Level=dtb.compatibility_level FROM master.sys.databases AS dtb WHERE dtb.name=@Database_Name IF ISNULL(@Compatibility_Level,0)<=80 BEGIN RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1) RETURN END Select Convert(varchar(Max),Substring([RowLog Contents 0],33,LEN([RowLog Contents 0]))) as [Script] from fn_dblog(NULL,NULL) Where [Operation]='LOP_DELETE_ROWS' And [Context]='LCX_MARK_AS_GHOST' And [AllocUnitName]='sys.sysobjvalues.clst' 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 GO --Execute the procedure like --EXEC Recover_Dropped_Data_Proc 'Database Name' ----EXAMPLE #1 : FOR ALL Dropped Objects EXEC Recover_Dropped_Objects_Proc 'test' --GO ------EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE EXEC Recover_Dropped_Objects_Proc 'test','2011/12/01','2013/01/30' --RESULT
First off, great articles. I learned a lot the past few days on reading transaction logs.
Just a question that maybe you can answer.
When I read the transaction logs with fn_dblog(), after a while I don’t get any records for a certain table (db is in Simple Recovery model). Probably the older transaction logs get inactive or marked as overwritable. I know those logs are not lost, there still readable until they get actually overwritten, because tools like Apex Sql Log can read them.
Do you know of a way to read these older (inactive) logs?
these return just object which delted and created by query work but if i delete to object by right click in mangement studio of mssql then it cannt recover why ,pls tell me difference (thanks in advance)
Thank you, it saves my time.
fn_dblog() not used in sql azure database. is there any option ?
Not sure about it. will check and update you.
Thanks for comment.
Hi Imran,
Any updates on Sql Azure database for Recovering the procedure,trigger,views ,tables etc.,
Awesome! You just saved me hours of time trying to re-create my objects.
Your answer is right
Thanks a lot bro…
Life saver! I’d dropped a serious proc and was able to restore it.
[…] https://raresql.wordpress.com/2012/12/04/sql-server-recover-the-dropped-objects-view-stored-procedure… […]
The best approach to do this. You saved my life, thanks a lot!!!!!!!
very nice
Can we get and recover table Script which has been remove or drop from database in sql server 2008 ?
plz… help me