我验证了下面的链接，因为有人（@Oliver）作为我的问题的重复项发布了。但此查询返回最后执行的脚本。这与我的问题无关。
最后执行的特定数据库查询 https://stackoverflow.com/questions/13638435/last-executed-queries-for-a-specific-database
我有一个包含 Sample_Training 的数据库，我在其中创建了一个存储过程，后来删除了它，现在我想检索该删除的存储过程。
我是公司员工，所以没有管理权限
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
通过上面的查询我可以获得存储过程，现在我的问题是如何获取函数、表。
我的问题得到了解决方案。首先需要创建一个过程
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'
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
