Open source · dbatools

Manage SQL Agent jobs and maintenance scripts with dbatools

The same backup and check jobs on every instance, the same version of the maintenance scripts everywhere: that is the target state. In everyday operations, instances drift away from it. Someone changed a job by hand on one server, a script is still at an older version, and after a migration jobs suddenly run twice.

Tested with dbatools 2.9.1 on Nov 18, 2026 · approx. 17 min read

Part 8 · Agent jobsSQL queries 2Lab Windows · Linux
Install-DbaMaintenanceSolution -LocalFilelab · Windows and Linuxarchive with …-main folderola-main.zipinstalled: 2026-09-12 from the archivetag archive from Ola’s GitHub page20260823_144645copy in the module2026-09-12Success2026-09-12 instead of 2026-08-23

dbatools has two groups of commands for this task. With Get-DbaAgentJob, Find-DbaAgentJob, Get-DbaAgentJobHistory and Copy-DbaAgentJob you search, compare and copy jobs across many instances. With Install-DbaMaintenanceSolution, Install-DbaFirstResponderKit and Install-DbaWhoIsActive you install three widely used community projects. This page shows which calls worked in the lab, what was actually installed or copied, and where you should double-check.

Most of our measurements ran on a Windows server with two instances. Where SQL Server on Linux behaves differently, the relevant section says so. This page does not answer whether and how often index maintenance pays off; that is the subject of our series Index maintenance explained.

Tested with dbatools 2.9.1 on Nov 18, 2026.

In 60 seconds

  • Install community scripts in a controlled way: download the release from the project page, record the checksum, install with -LocalFile and query the installed version afterward. The success message alone does not tell you which version is on the server.
  • -LocalFile for Ola Hallengren: dbatools only uses an archive containing the folder sql-server-maintenance-solution-main, as main.zip does. A tag archive from Ola's GitHub page or a missing file is silently discarded; the command installs its bundled copy and reports Success. If Windows reports no internet in its network profile, the command ignores -LocalFile entirely (simulated in the lab).
  • Ola on Windows: 11 Agent jobs, including "Output File Cleanup" with a CmdExec step, all without schedules. All jobs we started ran successfully. On Linux there are 10 jobs.
  • Copy-DbaAgentJob -DisableOnDestination disables the copied jobs, but their schedules stay enabled. If the owner login or the proxy is missing on the destination, the job is skipped with Skipped.
  • Find-DbaAgentJob does not combine filters: with several conditions, only one applies (open as #10853). -LastUsed finds jobs that never ran, not jobs that failed.
  • Output files: Get-DbaAgentJobHistory -WithOutputFile leaves the job name in the file name as a placeholder. On Windows, the path is also returned as a UNC path through the administrative share C$.

1 · Community scripts: origin, license, version

dbatools does not rebuild the widely used maintenance and diagnostic scripts; it incorporates the work of their authors. The three Install- commands download the scripts from the projects on GitHub and credit the authors in their help:

  • SQL Server Maintenance Solution by Ola Hallengren: backups, integrity checks, and index and statistics maintenance as stored procedures and Agent jobs. License MIT, documentation at ola.hallengren.com. Ola publishes no GitHub releases, only tags.
  • First Responder Kit by Brent Ozar Unlimited: sp_Blitz, sp_BlitzCache, sp_BlitzIndex and other diagnostic procedures. License MIT, with attribution notices for individual parts.
  • sp_WhoIsActive by Adam Machanic: shows what is running on the instance right now and who is blocking whom. License GPL-3.0.

All three are actively maintained by their authors and contributors; as of Nov 17, 2026, the latest change in each was no more than about two months old. For a maintenance script that runs on every server at night, you should therefore know which version is installed and where it came from.

Installing in a controlled way

  1. Download the release from the project page and store it as a file, including for servers without internet access.
  2. Record the checksum, for example with Get-FileHash -Algorithm SHA256.
  3. Install with -LocalFile, on a test instance first.
  4. Query the installed version and record it together with origin and checksum (sections 2 and 4).

In the lab, we downloaded these five archives on Nov 18, 2026 and checked them before every installation:

Get-FileHash C:\lab\pakete\*.zip -Algorithm SHA256
FileSourceVersionSHA-256
ola-main.zipMaintenance Solution, archive/refs/heads/main.zipVersion 2026-09-12 15:02:38e0cd31305a946ad1f86c09a13d215956a2e3ba25fb9a94c5c75355886d5e22e0
ola-20260912_150238.zipsame, tag 20260912_150238same version as main4a3598d595f7bacd6edd2e96c610bd3329677e1528efeadcada02fcaa899f20c
ola-20260823_144645.zipsame, tag 20260823_144645Version 2026-08-23 14:46:4548583527a9ed4326c6cef898a1db720460e49d547d1cb513a2252c2839666008
frk-20261114.zipFirst Responder Kit, release 202611148.35122fea767442f5c87dc4fc0ce900ca57671e260df9e23d452eb3be981e792957
whoisactive-20260409.zipsp_WhoIsActive, release 20260409202604092a486774b0efd18a7ba2a45b6bbff79d32c9a9ace6da679ed661aeb500efa125

The checksums were identical on Linux and Windows.

Where dbatools stores the scripts

The Install- commands unpack the scripts into a cache in the dbatools data folder, setting Path.DbatoolsData. On Windows, in the lab it was in the user profile, C:\Users\Administrator\AppData\Roaming\PowerShell\dbatools. Three properties are worth knowing:

  • An existing cache is reused unless you pass -Force or -LocalFile. This is known behavior (#10377). Once you have installed, the next call gets the same version, even if a newer one is available online.
  • dbatools ships its own copy of the Maintenance Solution (in the module under bin/maintenancesolution, version 2026-09-12 15:02:38 in 2.9.1). Install-DbaMaintenanceSolution falls back to it when something does not work (section 3).
  • The folder must exist. When Path.DbatoolsData pointed to a folder that did not exist in the lab, Install-DbaMaintenanceSolution installed nothing and reported neither a result nor a warning. Install-DbaFirstResponderKit stopped with Cannot find path '/tmp/cache-frk/SQL-Server-First-Responder-Kit-main' because it does not exist. This only affects a custom path; the default folder normally exists.

2 · Installing Ola Hallengren's Maintenance Solution

On the Windows server, we first installed the Maintenance Solution without -LocalFile, with jobs and the log table:

# Lab shortcut for self-signed certificates, this session only, connection stays encrypted (part 1)
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true

Install-DbaMaintenanceSolution -SqlInstance SQLSRV-01 -Database master -InstallJobs -LogToTable -CleanupTime 168 -Verbose
Lab output
VERBOSE: Performing the operation "Update local cached copy of the software" on target "MaintenanceSolution".
VERBOSE: GET https://github.com/olahallengren/sql-server-maintenance-solution/archive/main.zip with 0-byte payload
VERBOSE: received -1-byte response of content type application/zip

ComputerName InstanceName SqlInstance Results
------------ ------------ ----------- -------
SQLSRV-01    MSSQLSERVER  SQLSRV-01   Success

So without -LocalFile, the command downloads the current state of the main branch. Which version that is, Success does not say. The version is in the header of every procedure, for example dbo.DatabaseBackup:

$d = (Invoke-DbaQuery -SqlInstance SQLSRV-01 -Database master -As PSObject -Query "SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.DatabaseBackup')) AS d;").d
[regex]::Match("$d", 'Version: [0-9-]+ [0-9:]+').Value
Lab output
Version: 2026-09-12 15:02:38

We ran this query after every installation. It is the basis for all version numbers in section 3.

Which jobs are created

A query on msdb gives an overview of an instance's Agent jobs, with owner, number of schedules, subsystems and output file:

-- Agent jobs with owner, schedules, subsystems and output file (read-only, SQL Server 2017 and later)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT j.name AS job, j.enabled, SUSER_SNAME(j.owner_sid) AS owner,  (SELECT COUNT(*) FROM msdb.dbo.sysjobschedules AS js WHERE js.job_id = j.job_id) AS schedules,  (SELECT STRING_AGG(s.subsystem, ',') FROM msdb.dbo.sysjobsteps AS s WHERE s.job_id = j.job_id) AS subsystems,  (SELECT MAX(s.output_file_name) FROM msdb.dbo.sysjobsteps AS s WHERE s.job_id = j.job_id) AS output_fileFROM msdb.dbo.sysjobs AS jORDER BY j.nameOPTION (MAXDOP 1);

Result on the Windows server (excerpt, output file shortened):

Lab output
job                                       enabled owner schedules subsystems             output_file
---                                       ------- ----- --------- ----------             -----------
CommandLog Cleanup                              1 sa            0 TSQL                   $(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_…
DatabaseBackup - SYSTEM_DATABASES - FULL        1 sa            0 TSQL                   $(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_…
DatabaseBackup - USER_DATABASES - DIFF          1 sa            0 TSQL                   …
DatabaseBackup - USER_DATABASES - FULL          1 sa            0 TSQL                   …
DatabaseBackup - USER_DATABASES - LOG           1 sa            0 TSQL                   …
DatabaseIntegrityCheck - SYSTEM_DATABASES       1 sa            0 TSQL                   …
DatabaseIntegrityCheck - USER_DATABASES         1 sa            0 TSQL                   …
IndexOptimize - USER_DATABASES                  1 sa            0 TSQL                   …
Output File Cleanup                             1 sa            0 CMDEXEC                …
sp_delete_backuphistory                         1 sa            0 TSQL                   …
sp_purge_jobhistory                             1 sa            0 TSQL                   …
syspolicy_purge_history                         1 sa            1 TSQL,TSQL,PowerShell
  • 11 jobs from the Maintenance Solution, 10 with a T-SQL step and "Output File Cleanup" with a CmdExec step. syspolicy_purge_history belongs to SQL Server itself.
  • All jobs are enabled, but have no schedule. Without -AutoScheduleJobs, they only run once you create schedules. We did not test -AutoScheduleJobs.
  • The output file uses Agent tokens: log folder, job name, step, date and time. On the server, one such file was then named DatabaseBackup - USER_DATABASES - FULL_1_20261118_93852.txt.

We started four of the jobs with Start-DbaAgentJob -Wait:

Lab output
Name                                      CurrentRunStatus LastRunOutcome
----                                      ---------------- --------------
CommandLog Cleanup                                    Idle      Succeeded
DatabaseBackup - USER_DATABASES - FULL                Idle      Succeeded
DatabaseIntegrityCheck - SYSTEM_DATABASES             Idle      Succeeded
Output File Cleanup                                   Idle      Succeeded

Afterward, the log table dbo.CommandLog contained backups, RESTORE VERIFYONLY, DBCC CHECKDB and cleanup operations, all without errors. The job IndexOptimize - USER_DATABASES is installed as well. Whether, when and with which thresholds you schedule it is a separate question; see Index maintenance explained.

Parameters since version 2.8.0. Since 2.8.0, -Compress, -Verify and -CheckSum are no longer switches; they take the values Default, ForceOn, ForceOff or Remove (release note and source code 2.9.1, lines 294–314). Older installation scripts that set these parameters as switches therefore need an update. We did not try these parameters.

Deviation on Linux

On Linux, the same installation created 10 jobs on 2017, 2019, 2022 and 2025, all with T-SQL steps. "Output File Cleanup" is missing because the Agent does not run operating system commands there. Backups, checks and cleanup ran without errors. The output files are under /var/opt/mssql/log and are UTF-16 encoded. More on running SQL Server on Linux is in SQL Server on Linux for Windows admins.

3 · -LocalFile: what actually gets installed

According to the help, -LocalFile is meant for servers without internet access and for when you want to install a specific version. We installed different archives into a test database, each time with an empty cache, and queried the version afterward. The result was the same on Windows and Linux:

Case-LocalFileResultInstalled version
Aola-main.zip (folder …-main)Success2026-09-12 from the archive
Btag archive 20260912_150238Success2026-09-12 from the copy in the module
Ctag archive 20260823_144645Success2026-09-12 instead of 2026-08-23
Dfile does not existSuccess2026-09-12 from the copy in the module
Earchive from C, cache from A presentSuccess2026-08-23
Garchive from C, repacked as …-mainSuccess2026-08-23
Flike G, network profile without internet (simulated, Windows only)Success2026-09-12 instead of 2026-08-23

In cases B, C, D and F there was no warning. Only with -Verbose did a line appear, which looked like this for case D:

Lab output
VERBOSE: [09:37:24][Install-DbaMaintenanceSolution] No internet connection found, using included copy of Maintenance Solution.

The message does not match the cause: the server had internet access. What lies behind it in the 2.9.1 source code:

  • Only …-main is accepted. Save-DbaCommunitySoftware expects the folder sql-server-maintenance-solution-main in the archive (lines 433 and 441). A tag archive from Ola's GitHub page contains sql-server-maintenance-solution-20260823_144645 instead.
  • Every error silently falls back to the copy in the module (lines 386–396), even when the file is missing. If you specifically want to install an older version, you get the module's version (case C).
  • With an existing cache, the archive ends up as a subfolder in it. Afterward, there were two MaintenanceSolution.sql files. Because the command reads the files recursively (line 537), the version read last wins (case E). Later installations from this cache are affected as well.
  • Windows only: without internet in the network profile, -LocalFile does not count at all. If Get-NetConnectionProfile reports no internet connection, the command selects the copy in the module before it even looks at -LocalFile (lines 363–380). We simulated this by overriding Get-NetConnectionProfile in the session. A real server without internet access is still pending. It would affect exactly the case for which the help recommends -LocalFile.

What worked in the lab: use main.zip, or repack a tag archive into a folder …-main (case G):

# Repack a tag archive into a folder "sql-server-maintenance-solution-main"
Expand-Archive C:\lab\pakete\ola-20260823_144645.zip -DestinationPath C:\lab\w2\umpacken\x -Force
Rename-Item (Get-ChildItem C:\lab\w2\umpacken\x -Directory | Select-Object -First 1).FullName 'sql-server-maintenance-solution-main'
Add-Type -AssemblyName System.IO.Compression.FileSystem
[IO.Compression.ZipFile]::CreateFromDirectory('C:\lab\w2\umpacken\x', 'C:\lab\w2\umpacken\ola-20260823-als-main.zip')

Install-DbaMaintenanceSolution -SqlInstance SQLSRV-01 -Database ola_g -LocalFile C:\lab\w2\umpacken\ola-20260823-als-main.zip

The version query from section 2 then returned Version: 2026-08-23 14:46:45. Because the same call installs the copy in the module again when there is no internet profile (case F), one rule always applies: query the version after the installation. For several instances, you can run the same query against a list of instances.

As of Nov 18, 2026, the silent fallback had not been reported in the project. We did not search specifically for the case without an internet profile.

4 · First Responder Kit and sp_WhoIsActive

Both commands worked in the lab with the projects' release archives. dbatools renames their folders accordingly when unpacking.

$alle = 'SQLSRV-01', 'SQLSRV-01\SQL2'
Install-DbaFirstResponderKit -SqlInstance $alle -Database master -LocalFile C:\lab\pakete\frk-20261114.zip

On both instances, the command reported twelve objects as Installed: sp_Blitz, sp_BlitzAnalysis, sp_BlitzBackups, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, sp_BlitzLock, sp_BlitzWho, sp_DatabaseRestore, sp_ineachdb, sp_kill and SqlServerVersions. It does not install the scripts in the Deprecated folder. sp_Blitz itself returns the version:

DECLARE @v varchar(30), @d datetime;EXEC dbo.sp_Blitz @Version = @v OUTPUT, @VersionDate = @d OUTPUT, @VersionCheckMode = 1;SELECT @v AS v, CONVERT(char(10), @d, 23) AS d;

In the lab this returned 8.35 and 2026-11-14, on Windows as well as on all four Linux versions.

Install-DbaWhoIsActive -SqlInstance $alle -Database master -LocalFile C:\lab\pakete\whoisactive-20260409.zip |
    Select-Object SqlInstance, Name, Version, Status
Lab output
SqlInstance    Name           Version              Status
-----------    ----           -------              ------
SQLSRV-01      sp_WhoIsActive 2200.20260409\r\n(C) Installed
SQLSRV-01\SQL2 sp_WhoIsActive 2200.20260409\r\n(C) Installed
  • The Version column contains a line break and (C) from the script header (shown above as \r\n). For a report, cut the text off at the line break.
  • A different file depending on the SQL Server version. The archive contains separate versions for older releases. On 2017 and 2019, the command installed the file from the 2019 folder (1219.20260409), from 2022 on the file from the root (2200.20260409). We measured this for 2017 and 2019 only on Linux.
  • A single .sql file is only enough from SQL Server 2022 on. For 2017 and 2019, the command looks for the file in the 2019 subfolder and stops with a warning without installing anything: No SQL file found in /tmp/cache-wia-sql-sql2017/WhoIsActive. So pass the whole release archive.

To run it, there is Invoke-DbaWhoIsActive; we did not run that. How to read the locks and blocking that sp_WhoIsActive shows is explained in our locking hub.

5 · Copying jobs to other instances

For the test, we created six jobs on the source instance:

  • dbt_cmdexec with a CmdExec step, an output file and the schedule nachts,
  • dbt_powershell with a PowerShell step and the schedule nachts_ps,
  • dbt_proxy with a CmdExec step under a proxy account,
  • dbt_tsql_fehler with a T-SQL step that fails on purpose, and the hourly schedule stuendlich,
  • dbt_deaktiviert, already disabled on the source,
  • dbt_besitzer with an owner login that exists only on the source.

The proxy and the login did not exist on the destination. Try the call with -WhatIf first. This is how we copied:

Copy-DbaAgentJob -Source SQLSRV-01 -Destination SQLSRV-01\SQL2 `
    -Job dbt_cmdexec, dbt_powershell, dbt_proxy, dbt_tsql_fehler, dbt_deaktiviert, dbt_besitzer `
    -DisableOnDestination |
    Where-Object { $_ } |
    Format-Table DestinationServer, Name, Type, Status, Notes
Lab output
DestinationServer Name            Type      Status     Notes
----------------- ----            ----      ------     -----
SQLSRV-01\SQL2    dbt_besitzer    Agent Job Skipped    Job is dependent on login jobbesitzer
SQLSRV-01\SQL2    dbt_cmdexec     Agent Job Successful
SQLSRV-01\SQL2    dbt_deaktiviert Agent Job Successful
SQLSRV-01\SQL2    dbt_powershell  Agent Job Successful
SQLSRV-01\SQL2    dbt_proxy       Agent Job Skipped    Job is dependent on proxy dbt_proxy
SQLSRV-01\SQL2    dbt_tsql_fehler Agent Job Successful
  • Missing dependencies lead to Skipped with a clear reason. According to the source code, this also applies to missing operators and databases besides owner and proxy; the command also skips jobs that belong to maintenance plans. It ignores a missing owner only with -Force; we did not test that. Create logins and proxies on the destination first.
  • A second run skips existing jobs: Already exists on destination.
  • Where-Object { $_ } filters out empty objects that 2.9.1 writes between the result rows. This is fixed after 2.9.1 (#10795).

The job is off, the schedules are not

This is what the copied jobs looked like on the destination:

Get-DbaAgentJob -SqlInstance SQLSRV-01\SQL2 -Job dbt_cmdexec, dbt_deaktiviert, dbt_powershell, dbt_tsql_fehler |
    Format-Table Name, IsEnabled, HasSchedule,
        @{ n = 'Schedules'; e = { ($_.JobSchedules | ForEach-Object { "$($_.Name)=$($_.IsEnabled)" }) -join ',' } }
Lab output
Name            IsEnabled HasSchedule Schedules
----            --------- ----------- ---------
dbt_cmdexec         False        True nachts=True
dbt_deaktiviert     False       False
dbt_powershell      False        True nachts_ps=True
dbt_tsql_fehler     False        True stuendlich=True

-DisableOnDestination disables the job, not its schedules. As long as the job is off, that holds it back; in msdb, none of the three schedules had a next run (next_run_date 0). But whoever enables the job on the destination later also arms its schedules. In addition, the command first creates the job with the state from the source and only disables it afterward (source code 2.9.1, lines 536–537 and 576–582). We did not observe a run in this short gap and did not try to provoke one.

For a migration, this means: copy with -DisableOnDestination, check jobs and schedules on the destination, and switch over deliberately on cutover day, off on the source and on on the destination.

CmdExec, PowerShell and proxy

On Windows, all three step types ran on both instances. We started the proxy job only on the source, because it had not been copied. The history messages:

Lab output
SQLSRV-01 | dbt_cmdexec | Succeeded | Executed as user: NT Service\SQLSERVERAGENT. hallo aus cmdexec SQLSRV-01.  Process Exit Code 0.  The step succeeded.
SQLSRV-01 | dbt_powershell | Succeeded | Executed as user: NT Service\SQLSERVERAGENT. hallo aus PowerShell 5.1.20348.558 auf SQLSRV-01.  Process Exit Code 0.  The step succeeded.
SQLSRV-01 | dbt_proxy | Succeeded | Executed as user: SQLSRV-01\dbt_proxyuser. sqlsrv-01\dbt_proxyuser.  Process Exit Code 0.  The step succeeded.
SQLSRV-01\SQL2 | dbt_cmdexec | Succeeded | Executed as user: NT Service\SQLAgent$SQL2. hallo aus cmdexec SQLSRV-01.  Process Exit Code 0.  The step succeeded.
SQLSRV-01\SQL2 | dbt_powershell | Succeeded | Executed as user: NT Service\SQLAgent$SQL2. hallo aus PowerShell 5.1.20348.558 auf SQLSRV-01.  Process Exit Code 0.  The step succeeded.

The Agent's PowerShell step ran with Windows PowerShell 5.1, not PowerShell 7.

Deviation on Linux

On Linux, CmdExec jobs could be created and copied from 2019 to 2017, 2022 and 2025. Copy-DbaAgentJob does not check the subsystem and reported Successful. Only at run time did the step fail, the same way on all four versions:

Lab output
Running requested program is not allowed.  The step failed.

The Agent does load the subsystem, but Microsoft lists CmdExec and PowerShell as not supported on Linux. If you copy jobs from Windows to Linux, review them for subsystems first, for example with the query from section 2. -DisableOnDestination, the skipped dependencies and the filters from section 6 behaved on Linux exactly as on Windows.

6 · Searching jobs and finding failed steps

Find-DbaAgentJob searches many instances for jobs with certain properties. Each filter on its own returned what we expected in the lab. Combined, they did not:

Call (both instances)Hits among our test jobs
-IsFaileddbt_tsql_fehler on both instances
-IsDisableddbt_deaktiviert on the source, all four copied jobs on the destination
-IsNotScheduleddbt_besitzer, dbt_deaktiviert, dbt_proxy on the source, dbt_deaktiviert on the destination
-IsDisabled -IsNotScheduledthe same as -IsNotScheduled
-IsFailed -IsDisabledthe same as -IsDisabled
-StepName echodbt_cmdexec on both instances

With several condition filters, each filter resets the result, and only the last one in the code applies (source code 2.9.1, lines 271–317). This is reported in the project as #10853 and was open on Nov 18, 2026. On Linux, the same applied on all four versions. Combining the name filter with one condition worked there: -JobName dbt* -IsNotScheduled returned exactly the unscheduled test jobs. For further conditions, call the command with one filter and filter the result yourself.

-LastUsed does not find what the help promises. According to the help, -LastUsed 1 finds jobs that have not executed successfully in the last day. In the lab it returned only jobs that had never run, including the unscheduled Ola jobs. It did not find dbt_tsql_fehler, which had failed the same day. The command checks the date of the last run, not whether it succeeded. As of Nov 18, 2026, this had not been reported in the project. To find failed jobs, use -IsFailed.

History across all instances

Get-DbaAgentJobHistory takes a list of instances and jobs and returns the history in one call. Rows with StepID 0 summarize the whole job; to see the step messages, filter them out:

Get-DbaAgentJobHistory -SqlInstance $alle -Job dbt_cmdexec, dbt_powershell, dbt_proxy, dbt_tsql_fehler |
    Where-Object StepID -gt 0 |
    ForEach-Object { "$($_.SqlInstance) | $($_.Job) | $($_.Status) | $($_.Message)" }

Besides the success messages from section 5, the job that fails on purpose returned:

Lab output
SQLSRV-01 | dbt_tsql_fehler | Failed | Executed as user: NT SERVICE\SQLSERVERAGENT. dbt: gewollter Fehler [SQLSTATE 42000] (Error 50000).  The step failed.
SQLSRV-01\SQL2 | dbt_tsql_fehler | Failed | Executed as user: NT SERVICE\SQLAgent$SQL2. dbt: gewollter Fehler [SQLSTATE 42000] (Error 50000).  The step failed.

Without -EndDate, the command uses the clock of the machine running dbatools. If client and instance are in different time zones, this can shift the time range; a pull request on this is open (#10849). In the lab, there was no time zone difference.

7 · Finding the jobs' output files

Many jobs write their detailed output to a file, and Ola's jobs do too. For this, Get-DbaAgentJobHistory -WithOutputFile adds the columns OutputFileName and RemoteOutputFileName. For an Ola job on the Windows server:

Get-DbaAgentJobHistory -SqlInstance SQLSRV-01 -Job 'DatabaseBackup - USER_DATABASES - FULL' -WithOutputFile |
    Where-Object StepID -gt 0 |
    Format-List Job, Status, OutputFileName, RemoteOutputFileName
Lab output
Job                  : DatabaseBackup - USER_DATABASES - FULL
Status               : Succeeded
OutputFileName       : C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\$(ESCAPE_SQUOTE(JOBNAME))_1_20261118_93852.txt
RemoteOutputFileName : \\SQLSRV-01\C$\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\$(ESCAPE_SQUOTE(JOBNAME))_1_20261118_93852.txt
  • The command does not resolve the job name. Folder, step, date and time are replaced, but $(ESCAPE_SQUOTE(JOBNAME)) is still in the name. Neither path exists as given; Test-Path returned False. The actual file was named DatabaseBackup - USER_DATABASES - FULL_1_20261118_93852.txt. Our own job with a token behaved the same way. The help promises to resolve the tokens. As of Nov 18, 2026, this had not been reported in the project. Until then, insert the job name yourself.
  • RemoteOutputFileName goes through the administrative share C$. To open the file from another machine, you need administrator rights there.
  • On Linux, RemoteOutputFileName is the same local path as OutputFileName, not a UNC path. The job name remains unresolved there as well. A job with a fixed file name without tokens returned the correct path on Linux.

8 · Keeping jobs and scripts consistent

  • Record the origin: project, release or tag, checksum and license per script; install with -LocalFile, on a test instance first.
  • Query the version instead of trusting Success: Ola's procedure header, sp_Blitz @VersionCheckMode = 1, the version column of Install-DbaWhoIsActive. For Ola, this matters most on servers without internet access.
  • Keep an eye on the cache: without -Force or -LocalFile, dbatools uses whatever is already in its data folder.
  • Copy jobs with -DisableOnDestination and then check schedules and skipped jobs. Create logins (part 5) and proxies first.
  • One condition per Find-DbaAgentJob call, filter everything else yourself.
  • Output files: insert the job name into the path yourself.
  • On Linux: no CmdExec or PowerShell steps, even if copying succeeds.

What we measured and what we did not

  • Measured on Windows (Nov 18, 2026): dbatools 2.9.1 under Windows PowerShell 5.1 on Windows Server 2022 with two instances of SQL Server 2022 RTM on the same server, Windows authentication, run locally. The Maintenance Solution with jobs, the -LocalFile cases A to G, First Responder Kit, sp_WhoIsActive, six custom jobs copied to the second instance, history, output files and Find-DbaAgentJob. One run per case. The VM had one vCPU, so we do not quote run times.
  • Simulated: case F (network profile without internet) by overriding the function Get-NetConnectionProfile in the session. The code path matches the source code; a real server without internet access has not been tested.
  • Measured on Linux (Nov 18, 2026): dbatools 2.9.1 under PowerShell 7.6.8 against SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9 in Linux containers with SQL Server Agent. The same archives, cases A to E, CmdExec jobs copied from 2019 to the other versions, sp_WhoIsActive on 2017 and 2019.
  • Only from the source code: skipping for missing operators and databases and for maintenance plans, the new parameter form of -Compress, -Verify and -CheckSum.
  • Not measured: two separate Windows servers and domain accounts, -AutoScheduleJobs, -ReplaceExisting, Update-DbaMaintenanceSolution, Copy-DbaAgentJob -Force, the output of -WhatIf, Invoke-DbaWhoIsActive, time zone differences between client and instance, SQL Server 2017, 2019 and 2025 on Windows.

Further reading

More parts of the series