MS SQL maintenance plan not running if server is off

Question

I am maintaining an MS SQL 15.0 (2019) server, and part of that includes a nightly backup of the databases. I have a Maintenance Plan that is run by the SQL server agent just after midnight each night, which handles the backups, and it works pretty well.

However, last night the server was taken down for about 20 minutes and so it was off when the plan was supposed to run and I don't have my nightly backup from last night.

Is there any way to configure the maintenance plan to run when the server is started in the event that the server was turned off when the plan was supposed to run?

Answer 1

Due to my lack of know how in T-SQL, I personally use the PowerShell module dbatools for this purpose.

While I have ran this simple command with ansible on my SQL Servers, you can schedule it too with the MSSQL Agent or even the Task Scheduler with the "on startup"-schedule:

$jobname = "BackupJobName"
$interval = 24 ##in hours

if((Get-DbaAgentJob -SqlInstance localhost\MSSQLSERVER -Job $jobname).LastRunDate -le (Get-Date).addHours(-$interval)){
    write-host "Job did not run, will restart"
    Start-DbaAgentJob -SqlInstance localhost\MSSQLSERVER -Job $jobname} else {
    write-host "Job did run, no action required"}

Explanation: Basically I just compare the last run time with the current date/time minus the interval time of the job (which you have to enter manually). Not very advanced but simple and does the job.

I've not used this in production. If you plan on doing so, you might need to do some testing for unexpected behavior.

Answer 2

You could add a schedule to the job whose frequency is "on startup" and somehow add logic to the job to say "if the last backup is older than some threshold (e.g. 20 hours), proceed. otherwise, exit gracefully". The idea here is that since the job will also be executed on the regular daily schedule, the same logic needs to work there as well.

On a normal run, the job will start and say "it's been ≈24h since the last backup, proceed". If you're undergoing a maintenance window and the server starts at 01:00, it will say "it's been ≈25h since the last backup, proceed". But if the server has an unexpected restart at 12:00, it will say "it's only been ≈12h since the last backup, there's nothing for me to do".