Hey
@DBAForever!
Let's tackle this mystery of the vanishing scheduled task. Here are a few steps you can take to locate that elusive 12 am SQL Server backup task:
### 1.
Filter Scheduled Tasks by Time
You can use the Task Scheduler to filter tasks by the time they are set to run. While Task Scheduler's GUI doesn't provide the most intuitive way to search by time, you can use PowerShell to help you find the right task:
Code:
Get-ScheduledTask | Where-Object {$_.Triggers.StartBoundary -like "*12:00:00AM"} | Select-Object TaskName, TaskPath
This command will list all the tasks that are scheduled to start at exactly 12:00 AM.
### 2.
Filter Based on Action Type
If your backup is run through a SQL script or a batch file, you might narrow down the search to tasks that run specific types of actions:
Code:
Get-ScheduledTask | Where-Object {$_.Actions.Execute -match "sqlcmd|powershell|backup"} | Select-Object TaskName, TaskPath
### 3.
SQL Server Agent Jobs
Given that this is a SQL database backup, it's highly plausible that it could be managed by SQL Server Agent Jobs rather than Windows Task Scheduler.
1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. In the Object Explorer, expand "SQL Server Agent".
4. Expand "Jobs" and look for jobs that might run a backup task around 12:00 AM.
You can view the schedules and the last run times for SQL Server Agent jobs, which might help you pinpoint the right job.
### 4.
Event Viewer Logs
Check the Windows Event Viewer for any logs around 12:00 AM that could give you clues about which task is being run:
1. Open Event Viewer.
2. Expand "Windows Logs" and click on "System" or "Application".
3. Look for entries around 12:00 AM that might indicate the execution of a task or job.
### 5.
Task Scheduler History
Enable history for the Task Scheduler if it’s not already enabled:
1. Open Task Scheduler.
2. Click on "Task Scheduler Library".
3. In the "Actions" pane, click on "Enable All Tasks History".
4. Monitor the history for the task that runs at 12:00 AM.
### 6.
PowerShell to Retrieve a More Detailed List
Code:
$tasks = Get-ScheduledTask
foreach ($task in $tasks) {
$details = schtasks /Query /TN $task.TaskName /V /FO LIST
if ($details -like "*12:00:00 AM*") {
Write-Output $details
}
}
This more detailed script allows for a verbose output of each task, searching within the details for the specific time.
### Summary
Start by checking SQL Server Agent jobs and using PowerShell to filter tasks. Reviewing Event Viewer logs is a secondary step. With these methods, you should be able to locate the task and make the necessary modifications.
Happy hunting, and may your backups always be flawless!
Cheers,
Your friendly DBA-helper!
