Disabling a SQL Agent Job Using T-SQL

Yesterday, I had to run a full backup of a 764 GB database to clear up the differential backup that grew up to 600 GB (almost the same size as the database) because of enabling TDE on the database after a full restore.  

This database is being restored every first weekend of the month.  And we enable TDE encryption after.  A full backup is immediately run after. 

At a high level, here are the SQL Agent Job steps that we had:

1.  Restore the database from backup
2.  Enable TDE
3.  Run a Full database backup

The issue here is that after the TDE job step, the job goes on and immediately runs the full backup step.  This causes the next differential backup to be huge.  As it will change all the pages in the database after the full database backup.

To Remedy the issue, I had to add a few steps to the restore job, and I had to disable the differential backups until the TDE encryption was done.

At a high level, the job now looks like this:

1.  Disable the differential backup job
2.  Restore the database from backup
3.  Enable TDE (loop until the encryption is done)
4.  Run a Full database backup
5.  Enable the differential backup job

Here's the code I used to disable/enable the differential backup job:

 
EXEC dbo.sp_update_job 
    @job_name = N'Database Backup - DBNAME - Diff w Compress', 
    @enabled = 0  
GO 
 
EXEC dbo.sp_update_job 
    @job_name = N'Database Backup - DBNAME - Full w Stripe and Compress', 
    @enabled = 0  
GO  

Note that @enabled=0 means to disable, and @enabled=1 means to enable.

And as a bonus, here's the code I used to loop until the encryption is done:

 
DECLARE
       @encryptionState INT
 
BEGIN
 
SELECT @encryptionState = encryption_state
FROM sys.dm_database_encryption_keys
WHERE DB_ID('DBNAME') = database_id
 
 
IF @encryptionState = 3
       BEGIN
              SELECT 'DBNAME is encrypted.  Proceed to next step';
       END
ELSE
       BEGIN
              SELECT 'DBNAME is still encrypting.  Loop every hour to check'
              ;THROW 51000, 'Trying again next hour', 1
       END
 
END

I set it up to retry every hour for 3 days.