如何在SQL Server 2008中检索删除的存储过程，函数，表 [英] How to retrieve dropped stored procedure, function, table in SQL Server 2008

问题描述

我验证了以下链接，并将其链接为我的问题的副本(@Oliver).但是此查询返回上一个执行脚本.这和我的问题无关.

对特定数据库的上次执行查询

我有一个带有Sample_Training的数据库，并且在其中创建了一个存储过程，后来删除了它，现在我想检索该已删除的存储过程.

I have a database with Sample_Training 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

DECLARE @Date_From DATETIME = '2015-01-02'
DECLARE @Date_To DATETIME = '2015-01-05'

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

通过上述查询，我​​可以获取存储过程，现在我的问题是如何获取函数表.

By the above query I can get stored procedure and now my question is how to get function, table.

推荐答案

我已经解决了我的问题.首先需要创建一个过程

I got Solution to my question. First Need to create a procedure

CREATE PROCEDURE [dbo].[sp_Recover_Dropped_Objects]
    @Database_Name NVARCHAR(MAX),
    @Date_From DATETIME,
    @Date_To DATETIME
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 [Database Name],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

执行以下步骤

EXEC sp_Recover_Dropped_Objects 'Sample_Training','2015/12/24','2015/01/07'

这篇关于如何在SQL Server 2008中检索删除的存储过程，函数，表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！