MSSQL Server Store Procedure ile Full Backup Kontrol

Bu makalede Mssql Server üzerinde haftalık alınan backup jobların alınıp alınmadığını son kullanıcıya mail ile html formatında bildiren job’ı görmüş olacağız. Büyük sistemlerde Backup joblarımız gereğinden fazla olabilir. Mail ile bildirim almanıza rağmen Görünür den kaçma ihtimaline karşı aşağıda bulunan stored procedure yapısını oluşturup bir job aracılığıyla çalıştırmamız gerekmektedir. Bu sayede hangi veritabanı yedeğinin belirlenen saatte alınıp alınmadığını görebiliriz.

Öncelikle procedure yapısının çalışacağı stored procedure yapımız oluşturuyoruz:

CREATE PROCEDURE [dbo].[FullBackUpKontrol]
AS
BEGIN
    SET NOCOUNT ON;
    ---------------------------------------------------------------------
    -- 1) Sorguyu temp tabloya alınır.
    ---------------------------------------------------------------------
    IF OBJECT_ID('tempdb..#sonuc') IS NOT NULL DROP TABLE #sonuc;
	
	SELECT 
        p.database_name,
        p.[D] AS FullYedek
    INTO #sonuc
    FROM (
        SELECT database_name, type, backup_start_date
        FROM msdb.dbo.backupset
    ) b
    PIVOT (
        MAX(backup_start_date) FOR type IN ([D],[I],[L])
    ) AS p
    INNER JOIN master.sys.databases d
        ON p.database_name = d.name
    WHERE p.[D] < DATEADD(DAY, -3, GETDATE())
    ORDER BY p.database_name

    ---------------------------------------------------------------------
    -- 2) Tüm Full Yedekler alınmışsa girilecek job
    ---------------------------------------------------------------------
    IF NOT EXISTS (SELECT 1 FROM #sonuc)
    BEGIN
		EXEC msdb.dbo.sysmail_start_sp;
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name='SQLProfile',  -- MAIL PROFILINI BURAYA YAZ
            @recipients='xyunusyucelx@gmail.gov.tr', -- ALICI E-POSTA
            @subject='Full Backup Durumu',
            @body='Tüm Veri Tabanlarının FULL Yedeği son 3 gün içinde alınmıştır.';
        RETURN;
    END

    ---------------------------------------------------------------------
    -- 3) HTML tablo oluştur
    ---------------------------------------------------------------------
    DECLARE @html NVARCHAR(MAX) = 
    N'<h3> FULL Backup Alınamamış Veritabanları</h3>
      <table border="1" cellpadding="5" cellspacing="0">
      <tr>
          <th>Veri Tabanı</th>
          <th>Son Full Yedek</th>
      </tr>';

    SELECT @html = @html +
        N'<tr><td>' + database_name + N'</td><td>' + CONVERT(NVARCHAR(30), FullYedek, 120) + N'</td></tr>'
    FROM #sonuc;

    SET @html = @html + N'</table>';

    ---------------------------------------------------------------------
    -- 4) Mail'i gönder
    ---------------------------------------------------------------------
        EXEC msdb.dbo.sysmail_start_sp;
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SQLProfile',  -- MAIL PROFILINI BURAYA YAZ
        @recipients   = 'xyunusyucelx@gmail.gov.tr',
        @subject      = 'Full Backup Uyarı Raporu',
        @body_format  = 'HTML',
        @body         = @html;
END

Oluşturulan procedure yapısının primary veya sunucu üzerinde bulunan stand alone veritabanlarının full backup durumunu göstermek için aşağıdaki komutun 1. bölüme eklenmesi gerekmektedir.

    SELECT 
        p.database_name,
        p.[D] AS FullYedek
    INTO #sonuc
    FROM (
        SELECT database_name, type, backup_start_date
        FROM msdb.dbo.backupset
    ) b
    PIVOT (
        MAX(backup_start_date) FOR type IN ([D],[I],[L])
    ) AS p
    INNER JOIN master.sys.databases d ON p.database_name = d.name
 WHERE p.[D] < DATEADD(DAY, -3, GETDATE())
-- Sadece Primary olan veya AG üyesi olmayan DB'leri getirir:
AND (
    d.database_id NOT IN (SELECT database_id FROM sys.dm_hadr_database_replica_states)
    OR 
    d.database_id IN (
        SELECT drs.database_id 
        FROM sys.dm_hadr_database_replica_states drs
        JOIN sys.dm_hadr_availability_replica_states ars ON drs.replica_id = ars.replica_id
        WHERE ars.role = 1 -- 1 = PRIMARY
        AND ars.is_local = 1
    )
)

Yukarıda procedure üzerinde dikkat edilirse son 3 gün önce alınan backupların kontrolü yapılmaktadır. Oluşturulan job’ında bunan göre çalışması gerekmektedir.

Bir job oluşturulup stored procedure yapısının çalıştırılması gerekmektedir. Job’ın çalışması procedure çalışma zamanıyla uyumlu olmalıdır.

İlgili job çalıştırıldığında dönen sonuç aşağıda görülmektedir.

İlgili işlemi çalıştıracak job scripti:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'FULL_Backup_Kontrol', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Her Pazartesi Çalışan Bu Job. Cuma gününden itibaren alınmaya başlayan FULL Backup''larda eksik olup olmadığını göstermektedir.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', 
		@notify_email_operator_name=N'veritabanialarm_kullanicisi', --Mail Yollanacak Hesaplar @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DBAMON SP Calistir', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC dbo.FullBackUpKontrol', 
		@database_name=N'DBAMON', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Pazartesi Full BackUp Kontrol', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=2, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20251206, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'fe57c904-29c8-4855-b8f4-cd4d4af01408'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Bu makalede mssql server üzerinde alınan backupların alınıp alınmadığını kontrol eden yapıyı görmüş olduk. Başka makalede görüşmek dileğiyle..

“Emrolunduğun Gibi Dosdoğru Ol!” (Hûd: 112)