Restoring objects in SQL Server

This is a code googled in panic after occasional deletion of a stored procedure.

wordpress__recovery

Does real magic saving hours of work that could be spent on re-writing:

DECLARE
@Database_Name NVARCHAR(MAX) = 'SecretDbName',
@Date_From DATETIME = '21 Oct 2015',
@Date_To DATETIME = GETDATE()

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

 

Advertisements

About fdtki

Sr. BI Developer | An accomplished, quality-driven IT professional with over 16 years of experience in design, development and implementation of business requirements as a Microsoft SQL Server 6.5-2014 | Tabular/DAX | SSAS/MDX | Certified Tableau designer
This entry was posted in Administration, programming, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s