271 lines
8.1 KiB
Transact-SQL
271 lines
8.1 KiB
Transact-SQL
/*
|
|
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;
|