Cant Find Scheduled task that is Backing up SLQ Db

[DBAForever]

Hi experts.
Windows Server 2019
This is a SQL Server and the database shows o be getting backed up daily.
But I cannot find the scheduled Task that is performing the scheduled backup.
The backup runs at 12 am based on info I retrieve from SQL Server.

How can I sift thru the hundreds of scheduled task to find what runs at 12 am?
(I need to modify the task that's why I need to find it)

Thanks for any tips!

 

[ChatGPT]

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:

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:

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:
5. Open Event Viewer.
6. Expand "Windows Logs" and click on "System" or "Application".
7. 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:
8. Open Task Scheduler.
9. Click on "Task Scheduler Library".
10. In the "Actions" pane, click on "Enable All Tasks History".
11. Monitor the history for the task that runs at 12:00 AM.

    6. PowerShell to Retrieve a More Detailed List​

    $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!

 

[DBAForever]

... This is the Express Edition so there is no SQL JOb Agent. I omitted that essential fact

 

[Mike]

... This is the Express Edition so there is no SQL JOb Agent. I omitted that essential fact

If you know the elements of the tasks you can still filter them in PowerShell, but it sounds like some kind of nightmare. Instructions should still be valid, or have to use the GUI...

It is crazy how much automation still relies on just scheduled tasks in Windows or cron in Linux.

 

[ChatGPT]

Thanks for clarifying that you're using SQL Server Express Edition, which indeed lacks the SQL Server Agent (commonly used for scheduling jobs). This limits some of the built-in scheduling options for database backups, but don't worry—there are alternative ways to identify the task or create/manage backups outside of SQL Agent.
Here’s how you can tackle the issue:

---

Steps to Find the Scheduled Task Backing Up the SQL Database​

1. Check Scheduled Tasks in Windows Task Scheduler:
   • Since SQL Server Express doesn’t have SQL Agent, a Windows Task Scheduler job is likely responsible for running the backup.
   • Steps:
     1. Open Task Scheduler (Win + R, type taskschd.msc, press Enter).
     2. Look for a task that runs a script or command at regular intervals:
        • It may call sqlcmd or run a backup .bat script.
     3. Right-click > Properties on suspicious tasks and inspect the Actions tab for details.

---

1. Search for Backup Scripts or Commands:
   • Commonly, backups on SQL Server Express are triggered using sqlcmd or custom .bat files. Locate these files:
     • Check C:\Windows\Tasks or C:\TaskScheduler for custom scheduler task files.
     • Search your system for .bat or .sql files related to SQL commands:
       

       cmd dir C:\ /s /p *.bat dir C:\ /s /p *.sql

---

1. Inspect SQL Server Logs
   • Backup operations leave traces in the SQL Server log. Use this to verify when backups are occurring and whether Task Scheduler is initiating them.
   • Steps:
     1. Open SQL Server Management Studio (SSMS) or connect to the database using sqlcmd.
     2. Run this query to check recent backup history:
        

        sql SELECT database_name, backup_start_date, backup_finish_date, type, physical_device_name FROM msdb.dbo.backupset INNER JOIN msdb.dbo.backupmediafamily ON backupset.media_set_id = backupmediafamily.media_set_id ORDER BY backup_finish_date DESC

        • Note: On SQL Express, this might not work directly if you're not using msdb (which is often limited).

---

1. Custom Backup Programs or Scripts:
   • Verify if third-party software (e.g., AOMEI, Veeam, or other backup utilities) is responsible.
   • Check installed applications for any recognizably named backup software.

---

1. Use SQLCMD for Backup Management:
   • If no existing task can be located, create a simple .bat script and schedule it with Task Scheduler for future backups.
   • Example .bat script:
     

     cmd sqlcmd -S .\SQLEXPRESS -E -Q "BACKUP DATABASE YourDatabaseName TO DISK='C:\Backups\YourDatabaseName.bak' WITH INIT;"

   • Configure Task Scheduler:
     • Set this script to run daily, weekly, etc.

---

If the Task or Job Remains Undetected​

• Check Event Viewer:
  • Look under Windows Logs > Application to locate recent events related to "backup" or SQL operations.
• Manually Inspect Backups:
  • Determine the exact path of existing backups files, reviewing timestamps for clues about when the task is executing.

---

Feel free to share what you uncover! We can refine these steps further or set up a new scheduled task if no others can be found.