sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | If an index is missing and in need to create it because of a restoration!!!

IF ((SELECT 1 FROM MASTER.SYS.all_objects O WHERE O.name = 'fn_ifNeedFullBackup') IS NULL)
BEGIN
use master
declare @fnsql nvarchar(max) = '
CREATE FUNCTION [fn_ifNeedFullBackup] (
@DBName sysname)
RETURNS BIT
AS
BEGIN
DECLARE @NeedFull BIT;
DECLARE @LastLogBackupLSN NUMERIC (25,0);
DECLARE @RecoveryModel TINYINT;

SELECT
@LastLogBackupLSN = [last_log_backup_lsn]
FROM
sys.database_recovery_status
WHERE
[database_id] = DB_ID (@DBName);

SELECT
@RecoveryModel = [recovery_model]
FROM
sys.databases
WHERE
[database_id] = DB_ID (@DBName);

IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)
SELECT @NeedFull = 0
ELSE
SELECT @NeedFull = 1;

RETURN (@NeedFull);
END;'
exec (@fnsql)
end

EXEC SP_MSFOREACHDB 'USE [?];
IF (DB_NAME() IN (''IP24162_TEST'', ''GA24190_Test'', ''UCP_JDP_Test'', ''OS20042_Test'', ''YM24164_TEST''))
BEGIN
IF ( SELECT 1 FROM sys.indexes WHERE NAME = ''JOB__PROC_QUEUE'') IS NULL
BEGIN
BEGIN TRY
print ''[?] : JOB__PROC_QUEUE''
DECLARE @SQL NVARCHAR(MAX) = ''
CREATE NONCLUSTERED INDEX [JOB__PROC_QUEUE] ON [dbo].[job] 
(
[process_id] ASC,
[queue] ASC,
[status] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]''
EXEC (@SQL)
END TRY
BEGIN CATCH
DECLARE @email varchar(500) = ''AYEH@digitalriver.com'',
@profile_name varchar(100), @message varchar(8000)
SELECT TOP 1 @profile_name = name 
FROM msdb.dbo.sysmail_profile 
WHERE name IN (''DRSQLSVC'', ''Commerce5 Admin profile'')
SELECT @message = ''JOB__PROC_QUEUE FAILED''

EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @profile_name, @recipients = @email, @body = @message, @subject = ''PLEASE GO TO THK TST TO SEE INDEX [JOB__PROC_QUEUE] IN [?]''
END CATCH
END
END'

EXEC SP_MSFOREACHDB 'USE [?];
IF (DB_NAME() IN (''IP24162_TEST'', ''GA24190_Test''))
BEGIN
PRINT ''[?] : CHECK IN DB [?]''
DECLARE @CNT INT
;WITH A AS (SELECT DISTINCT 
obj.name TableName, si.name AS IndexName, sc.Name AS ColumnName, sic.Index_ID
, sic.is_included_column [key/Include]
FROM sys.indexes si 
INNER JOIN sys.index_columns sic ON sic.OBJECT_ID = si.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON si.OBJECT_ID = sc.OBJECT_ID AND sc.Column_ID = sic.Column_ID
INNER JOIN sys.objects obj ON obj.object_id = si.object_id
WHERE obj.name = ''JOB'' AND si.name = ''JOB__PROC_FK'')
SELECT @CNT = COUNT([KEY/INCLUDE]) FROM A WHERE [KEY/INCLUDE] = 0
IF (((SELECT MASTER.DBO.fn_ifNeedFullBackup(''?'')) = 1) AND (@CNT <> 3))
BEGIN
print ''[?] : JOB__PROC_FK''
BEGIN TRY
DECLARE @SQL NVARCHAR(MAX) = ''
DROP INDEX JOB__PROC_FK ON JOB
CREATE NONCLUSTERED INDEX [JOB__PROC_FK] ON [dbo].[job] ([process_id] ASC,[status] ASC,[end_job_datetime] ASC)
INCLUDE ( [job_id],[cutoff_date],[drop_date],[n_candidate_records],[n_selected_records],[start_date_time],
[n_updated_records],[description],[job_priority],[job_description],[has_errors],[manual_review_fulfillment],
[step_number],[step_name],[last_pulse],[queue]) WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]''
EXEC (@SQL)
END TRY
BEGIN CATCH
DECLARE @email varchar(500) = ''AYEH@digitalriver.com'',
@profile_name varchar(100), @message varchar(8000)
SELECT TOP 1 @profile_name = name 
FROM msdb.dbo.sysmail_profile 
WHERE name IN (''DRSQLSVC'', ''Commerce5 Admin profile'')
SELECT @message = (''JOB__PROC_FK FAILED '')
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @profile_name, @recipients = @email, @body = @message, @subject = ''PLEASE GO TO THK TST TO SEE INDEX [JOB__PROC_FK] IN [?]''
END CATCH
END
END'
Comments