/*
Server:		ssunb006db01.sunstore.ch
Format:		GCM 
Business:	TPPHAR
type:		VALI
Version:	22.1.11010.00065

24.10.2022, TSC
*/

USE master;

BEGIN TRANSACTION;
SET XACT_ABORT ON;
SET NOCOUNT ON;

--#region variables
DECLARE @dbs TABLE (dbName VARCHAR(400),
                    have_commvault_bkp BIT
                        DEFAULT 0,
                    have_system_bkp BIT
                        DEFAULT 0,
                    last_system_backup DATETIME,
                    backup_type VARCHAR(50) NULL);
DECLARE @tbl_log_msg TABLE (tstamp DATETIME NOT NULL
                                    DEFAULT CURRENT_TIMESTAMP,
                                msg VARCHAR(500) NOT NULL);

--#endregion

--#region fetch list of db's
INSERT INTO @dbs (dbName)
SELECT name
  FROM sys.databases d
 WHERE name NOT IN ( N'master', N'tempdb', N'model', N'msdb' );
--#endregion

--#region fetch backups in the last week
IF OBJECT_ID('tempdb..#bkps') IS NOT NULL
BEGIN
    DROP TABLE #bkps;
END

SELECT      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER,
            bs.database_name,
            bs.backup_start_date,
            bs.backup_finish_date,
            bs.expiration_date,
            bs.backup_size,
            mf.logical_device_name,
            mf.physical_device_name,
            bs.name AS backupset_name,
            bs.description,
            CASE
                 WHEN bs.type = 'D' THEN 'Database'
                 WHEN bs.type = 'I' THEN 'Differential'
                 WHEN bs.type = 'L' THEN 'Log'
                 WHEN bs.type = 'F' THEN 'File or filegroup'
                 WHEN bs.type = 'G' THEN 'Differential file'
                 WHEN bs.type = 'P' THEN 'Partial'
                 WHEN bs.type = 'Q' THEN 'Differential partial'
                 ELSE 'Unknown' END AS backup_type,
            CASE
                 WHEN CHARINDEX(':', mf.physical_device_name, 0) > 0 THEN 1
                 WHEN mf.logical_device_name IS NOT NULL THEN 2
                 ELSE 0 END AS to_file,
            CASE
                 WHEN bs.name LIKE '%commvault%'
                  AND CHARINDEX(':', mf.physical_device_name, 0) = 0
                  AND mf.logical_device_name IS NULL THEN 1
                 ELSE 0 END AS is_commVault_bkp
  INTO      #bkps
  FROM      msdb.dbo.backupmediafamily mf
 INNER JOIN msdb.dbo.backupset bs
    ON mf.media_set_id = bs.media_set_id
 WHERE      (CONVERT(DATETIME, bs.backup_start_date, 102) >= GETDATE() - 7)
   AND      bs.type                                       = 'D' --ignore log backups
 ORDER BY bs.database_name,
          bs.backup_finish_date;
--#endregion

--#region Look for commvault backups for each databases
UPDATE      d
   SET      d.have_commvault_bkp = 1,
            d.backup_type = b.backup_type
  FROM      @dbs d
 INNER JOIN #bkps b
    ON b.database_name = d.dbName
 WHERE      b.is_commVault_bkp = 1;
--#endregion 

--#region Look for system backups for each databases
UPDATE      d
   SET      d.have_system_bkp = 1,
            d.last_system_backup = lastBkp.backup_finish_date,
            d.backup_type = b.backup_type
  FROM      @dbs d
 INNER JOIN #bkps b
    ON b.database_name       = d.dbName
 INNER JOIN (   SELECT b2.database_name,
                       MAX(b2.backup_finish_date) AS backup_finish_date
                  FROM #bkps b2
                 GROUP BY b2.database_name) lastBkp
    ON lastBkp.database_name = b.database_name
 WHERE      b.is_commVault_bkp = 0;
--#endregion 


SELECT d.dbName,
       d.have_commvault_bkp,
       d.have_system_bkp,
       d.last_system_backup,
	   d.backup_type
  FROM @dbs d;


--#region check if we are on a REF or AAI server
DECLARE @is_ref_srv BIT = 0;
DECLARE @is_aai_srv BIT = 0;

IF (@@SERVERNAME IN (
    --sun
    'ssunbrefde02.sunstore.ch\apssql', 'ssunbrefde02\apssql', 'ssunbreffr02.sunstore.ch\apssql', 'ssunbreffr02\apssql',
    --cvi
    'scvnbrefdb01.coop-vitality.ch\apssql', 'scvnbrefdb01\apssql', 'scvnbrefdb02.coop-vitality.ch\apssql', 'scvnbrefdb02\apssql', 
	--ama
    'samnbrefde02.amavita.ch\apssql', 'samnbrefde02\apssql', 'samnbreffr02.amavita.ch\apssql', 'samnbreffr02\apssql',
	--aai, should not be used anyhow
	'saainbref02.aai.local\apssql', 'saainbref02\apssql'))
BEGIN
    SET @is_ref_srv = 1;
    PRINT 'We are on a REF server.';
END


IF(OBJECT_ID('master.cfg.Identity'))IS NOT NULL BEGIN 
	IF EXISTS(SELECT 1 FROM master.cfg.[Identity] [i] WHERE i.[Format] = 'AAI')
	BEGIN
		SET @is_aai_srv = 1;
		PRINT 'We are on a AAI server.'
	END 
END 
ELSE BEGIN
	PRINT 'No table identity in master, skipping AAI detection.'
END 
--#endregion

--#region populate list of jobs to drop and fetch job_id
DECLARE @tbl_job_name TABLE (job_id UNIQUEIDENTIFIER NULL,
                             job_name VARCHAR(500) NOT NULL,
                             treated BIT NOT NULL
                                 DEFAULT 0);

INSERT INTO @tbl_job_name (job_name)
VALUES ('D91040 - Backup of simple databases'),
       ('D91050 - Backup of full databases'),
       ('D91010 - Backup of several databases'),
       ('DR91020 - Log-backup');

UPDATE      tjn
   SET      tjn.job_id = j.job_id
  FROM      msdb.dbo.sysjobs j
 INNER JOIN @tbl_job_name tjn
    ON tjn.job_name = j.name;
--#endregion

--#region Check for unknown backup job
INSERT INTO @tbl_log_msg (tstamp,msg)
SELECT DISTINCT 
	CURRENT_TIMESTAMP AS tstamp, 
	'job doing a "backup database ... to file" ' + j.name AS msg
--SELECT      
--		DISTINCT 'Jobs making a backup not in the recognized jobs' AS msg,
--		j.name,
--		j.enabled,
--		s.schedule_id,
--		LTRIM(SUBSTRING(js.command, CHARINDEX('to disk', js.command) - 40, 100)) AS command_fragment
  FROM      msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobsteps js
    ON js.job_id = j.job_id
 INNER JOIN msdb.dbo.sysjobschedules s
    ON s.job_id  = j.job_id
 WHERE      js.command LIKE '%backup database%to disk%'
   AND      j.name <> 'D91030 - Backup ActivePos_Read'
   AND      NOT EXISTS (SELECT 1 FROM @tbl_job_name tjn WHERE tjn.job_name = j.name);
--#endregion

--#region check that all db's have commvault backups before dropping jobs
DECLARE @is_commvault_active BIT = 0;
SELECT @is_commvault_active =  CASE 
		WHEN MAX(dbCnt.cnt) = COUNT(1) AND MAX(dbCnt.cnt) > 0 THEN 1
		ELSE 0
	END
FROM @dbs d
	CROSS APPLY (
		SELECT COUNT(1) AS cnt
		FROM @dbs d2
	) AS dbCnt
WHERE d.have_commvault_bkp = 1
;

If @is_commvault_active = 0
	PRINT 'Commvault is not active on all db''s.'
--#endregion

IF @is_ref_srv = 0 AND @is_aai_srv = 0 AND @is_commvault_active = 1
BEGIN
    --#region delete jobs known to be stopped

    /* declare variables */
    DECLARE @job_id   UNIQUEIDENTIFIER,
            @job_name VARCHAR(500);

    DECLARE delDisabledBkpJob CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT tjn.job_id,
           tjn.job_name
      FROM @tbl_job_name tjn
     WHERE tjn.treated = 0
       AND tjn.job_id IS NOT NULL;

    OPEN delDisabledBkpJob;

    FETCH NEXT FROM delDisabledBkpJob
     INTO @job_id,
          @job_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC msdb.dbo.sp_delete_job @job_id = @job_id, -- uniqueidentifier
                                    @job_name = NULL, -- sysname
                                    @originating_server = NULL, -- sysname
                                    @delete_history = 1, -- bit
                                    @delete_unused_schedule = 1; -- bit

        INSERT INTO @tbl_log_msg (tstamp,msg)
        VALUES (GETDATE(), 'Dropped job ' + @job_name);
        UPDATE @tbl_job_name
           SET treated = 1
         WHERE job_id = @job_id;

        PRINT 'Deleted job ' + @job_name + ' (job id: ' + CONVERT(VARCHAR(100), @job_id) + ')';
        FETCH NEXT FROM delDisabledBkpJob
         INTO @job_id,
              @job_name;
    END

    CLOSE delDisabledBkpJob;
    DEALLOCATE delDisabledBkpJob;

--#endregion
END
ELSE BEGIN 
	INSERT INTO @tbl_log_msg (tstamp,msg)
    SELECT 
		CURRENT_TIMESTAMP
		,CASE
			WHEN @is_ref_srv = 1 THEN 'Not dropping jobs on pharmacy reference servers...'
			WHEN @is_aai_srv = 1 THEN 'Not dropping jobs on AAI servers...'
			WHEN @is_commvault_active = 0 THEN 'Not dropping jobs where CommVault is not active...'
			ELSE 'Skipping job deletion' 
		END AS msg
	;
END

SELECT 
	tld.tstamp,
    tld.msg
FROM @tbl_log_msg tld;

ROLLBACK TRANSACTION;