Database backups without SQL Agent: Task Scheduler and scripted log transfer

A server running SQL Server Express has hosted the CRM system database for several years. The Maintenance Plan Wizard is unavailable because this edition has no SQL Server Agent, so an administrator took backups by hand before application updates. The latest .bak file is eight months old, and the database, in the FULL recovery model, has a log several times larger than its data file, because nobody has ever taken a log backup.

No Agent does not mean no backups. BACKUP and RESTORE work in Express exactly as in the higher editions; what is missing is only the mechanism that runs them on time. This post shows how to replace it with Task Scheduler, a PowerShell script with sqlcmd and a gMSA, and how to restore log backups on a second server as a home-grown equivalent of log shipping.

What Express lacks and what replaces it

The SQL Server 2025 and 2022 editions feature tables list several backup-related features that Express does not include:

Feature	Express	Replacement
SQL Server Agent, maintenance plans	not available	Windows Task Scheduler and a PowerShell script
Built-in log shipping	not available	a script that copies log backup files and a restore job on a second server
Backup compression (COMPRESSION)	not available	disk and share capacity sized for full-size files
Backup encryption (ENCRYPTION)	not available	NTFS permissions on the folder and share, volume encryption
Database Mail	not available	the job result code and the age of the latest backup in the monitoring system
BACKUP and RESTORE: full, differential and log backups	available	not applicable

A .bak file from Express can be restored on any edition of the same or a newer version. Log backups apply to databases in the FULL recovery model, and new databases inherit the recovery model from the model database, whose default depends on the edition. The difference between SIMPLE and FULL is covered in the fourth part of this guide.

The job account and its permissions

The backup jobs run under a separate gMSA, CORP\svc-sqlbackup$, rather than the service account CORP\svc-sql$, so taking over the job does not give anyone the identity of the database engine. The account is created the same way as the service account, with the GG-SQL-Hosts group as the list of servers allowed to retrieve the password (the fifth part of this guide). Test-ADServiceAccount -Identity svc-sqlbackup should return True on both servers. In SQL Server the account gets a login and the db_backupoperator role in every database, which means the BACKUP DATABASE, BACKUP LOG and CHECKPOINT permissions without access to the data:

CREATE LOGIN [CORP\svc-sqlbackup$] FROM WINDOWS;

-- User with the db_backupoperator role in every database except tempdb
DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'USE ' + QUOTENAME(name) + N'; CREATE USER [CORP\svc-sqlbackup$] FOR LOGIN [CORP\svc-sqlbackup$]; '
             + N'ALTER ROLE db_backupoperator ADD MEMBER [CORP\svc-sqlbackup$];' + NCHAR(10)
FROM sys.databases
WHERE name <> N'tempdb' AND state_desc = N'ONLINE';
EXEC sys.sp_executesql @sql;

The backup file is written by the SQL Server process, not by the job account. The write permission therefore goes to the instance’s service SID, NT SERVICE\MSSQL$CRM, which covers only this instance and not ERP and HR running under the same gMSA. The job account creates the database folders, copies files to the share and deletes old ones:

New-Item -ItemType Directory -Path 'D:\SQLBackup\CRM' -Force | Out-Null
# Job account: creating folders, copying and deleting files
icacls 'D:\SQLBackup' /grant 'CORP\svc-sqlbackup$:(OI)(CI)M'
# CRM instance: writing and reading backup files
icacls 'D:\SQLBackup\CRM' /grant 'NT SERVICE\MSSQL$CRM:(OI)(CI)M'

# Share on the server that receives the copies, here srv-sql-02
New-SmbShare -Name 'SQLBackup' -Path 'D:\SQLBackup' -ChangeAccess 'CORP\svc-sqlbackup$' -ReadAccess 'CORP\GG-SQL-Admins'

The backup script

-Type Full takes a full backup of every database except tempdb, system databases included. -Type Log covers databases in FULL whose log backup chain has started: last_log_backup_lsn in sys.database_recovery_status is NULL when the database has no full backup yet, and BACKUP LOG would then fail with error 4214. A new database therefore gets its first log backup after the next full backup.

param(
    [Parameter(Mandatory)][ValidateSet('Full', 'Log')][string]$Type,
    [string]$Instance = "$env:COMPUTERNAME,14331",
    [string]$Root = 'D:\SQLBackup\CRM',
    [string]$Share = '\\srv-sql-02\SQLBackup\CRM',
    [int]$RetentionDays = 14
)
$stamp  = Get-Date -Format 'yyyyMMdd_HHmmss'
$log    = Join-Path $Root ('backup_{0}_{1:yyyyMM}.log' -f $Type, (Get-Date))
$failed = 0

# Log backup: FULL databases with a started chain; full backup: all except tempdb
$filter = "d.recovery_model_desc = 'FULL' AND r.last_log_backup_lsn IS NOT NULL"
if ($Type -eq 'Full') { $filter = "d.name <> 'tempdb'" }
$query = "SET NOCOUNT ON; SELECT d.name FROM sys.databases AS d " +
         "JOIN sys.database_recovery_status AS r ON r.database_id = d.database_id " +
         "WHERE d.state_desc = 'ONLINE' AND $filter;"
$databases = sqlcmd -S $Instance -E -C -b -h -1 -W -w 1024 -Q $query | Where-Object { $_ }
if ($LASTEXITCODE -ne 0) { exit 1 }

foreach ($db in $databases) {
    $dir = Join-Path $Root $db
    New-Item -ItemType Directory -Path $dir -Force | Out-Null
    if ($Type -eq 'Full') {
        $sql = "BACKUP DATABASE [$db] TO DISK = N'$dir\${db}_${stamp}_FULL.bak' WITH CHECKSUM, INIT;"
    } else {
        $sql = "BACKUP LOG [$db] TO DISK = N'$dir\${db}_${stamp}_LOG.trn' WITH CHECKSUM, INIT;"
    }
    sqlcmd -S $Instance -E -C -b -Q $sql | Out-File -FilePath $log -Append -Encoding utf8
    if ($LASTEXITCODE -ne 0) { $failed++ }
}

The script connects to the local instance by port rather than through the sql-crm name, so that after the A record is switched, the job on the old server does not start backing up the new server to its own disk. File names take the form CrmDB_20261018_101500_LOG.trn, so alphabetical order is the order of execution, which both retention and restore rely on.

WITH CHECKSUM verifies page checksums and writes a checksum of the whole backup, which RESTORE verifies later; a page with a bad checksum stops the backup. The -b switch gives exit code 1 for an error with a severity above 10, and -C trusts the server certificate without validation, which is needed with a self-signed certificate because newer versions of sqlcmd require an encrypted connection by default. The script’s exit code 1 reaches Task Scheduler as the last run result and, without Database Mail, is the main signal for monitoring.

# Copy to the share: only .trn files for a log backup, .bak as well for a full one
$mask = if ($Type -eq 'Full') { '*.bak', '*.trn' } else { '*.trn' }
robocopy $Root $Share $mask /S /R:3 /W:10 /NP /NJH /NJS "/LOG+:$log" | Out-Null
if ($LASTEXITCODE -ge 8) { $failed++ }

# Retention after a successful full backup: files older than N days and older than the latest full backup
if ($Type -eq 'Full' -and $failed -eq 0) {
    $limit = (Get-Date).AddDays(-$RetentionDays)
    foreach ($folder in Get-ChildItem -Path $Root -Directory) {
        $files = Get-ChildItem -Path $folder.FullName -File | Where-Object Extension -in '.bak', '.trn' | Sort-Object Name
        $lastFull = $files | Where-Object Name -like '*_FULL.bak' | Select-Object -Last 1
        if (-not $lastFull) { continue }
        $files | Where-Object { $_.Name -lt $lastFull.Name -and $_.LastWriteTime -lt $limit } | Remove-Item
    }
}
if ($failed -gt 0) { exit 1 }
exit 0

Local disk first, then the share

Backing up straight to the share works too, but it needs write access on the share for the service account CORP\svc-sql$, and an unavailable share then means no backup at all. A local backup copied immediately afterwards gives a fast restore from the server’s own disk and a copy that survives the server’s failure, and a gap of a few seconds does not increase data loss. A robocopy exit code of 8 or higher means that at least one file was not copied. For a log backup the script copies only .trn files, so that it does not touch a .bak that the nightly full backup may be writing at that moment.

Retention runs only after a successful full backup and deletes files older than 14 days, but only those from before the latest full backup, so that backup and the log backups after it always stay. Before the first run it is worth adding -WhatIf to Remove-Item. On srv-sql-02 a separate job runs the same loop. The share on the second SQL server sits in the same domain, so a copy out of reach of domain accounts is a separate part of the plan.

Task Scheduler under a gMSA

A gMSA has no password to type into the wizard, so the job is registered in PowerShell, with -LogonType Password and no password supplied:

$principal = New-ScheduledTaskPrincipal -UserId 'CORP\svc-sqlbackup$' -LogonType Password
$script    = 'C:\Scripts\Backup-SqlInstance.ps1'

$fullAction  = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument "-NoProfile -NonInteractive -File $script -Type Full"
$fullTrigger = New-ScheduledTaskTrigger -Daily -At '01:00'
Register-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - FULL' -Action $fullAction -Trigger $fullTrigger -Principal $principal

# Repeat every 15 minutes; without -RepetitionDuration the pattern repeats indefinitely
$logAction  = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument "-NoProfile -NonInteractive -File $script -Type Log"
$logTrigger = New-ScheduledTaskTrigger -Once -At '00:05' -RepetitionInterval (New-TimeSpan -Minutes 15)
Register-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - LOG' -Action $logAction -Trigger $logTrigger -Principal $principal

Starting at 00:05 keeps the log backups apart from the full backup at 01:00, and the default MultipleInstances setting of IgnoreNew does not start a job that is still running.

The second server: restoring log backups

srv-sql-02 runs a CRM instance on the same SQL Server version, on port 14331 and under CORP\svc-sql$, so after failover neither the application connection string nor the SPN from the second part of this guide changes. A backup from a newer version cannot be restored on an older one, and STANDBY does not work when the database needs upgrading to a newer version, which is why both instances are updated together.

The job account gets the same permission block here as on srv-sql-01, because backups will run from this server after failover, plus two more. RESTORE of an existing database can be run only by members of the sysadmin and dbcreator roles and by the database owner:

-- RESTORE of an existing database requires the dbcreator role
ALTER SERVER ROLE dbcreator ADD MEMBER [CORP\svc-sqlbackup$];
-- Reading restore history in msdb
USE [msdb];
ALTER ROLE db_datareader ADD MEMBER [CORP\svc-sqlbackup$];

The database is initialised once, from a full backup restored without recovery. If the file paths differ from srv-sql-01, the statement needs the MOVE option with the logical names from RESTORE FILELISTONLY.

-- Without recovery: the database stays RESTORING and accepts further log backups
RESTORE DATABASE [CrmDB]
FROM DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_010000_FULL.bak'
WITH NORECOVERY;

param(
    [string]$Instance = "$env:COMPUTERNAME,14331",
    [string]$Root = 'D:\SQLBackup\CRM'
)
$log    = Join-Path $Root ('restore_{0:yyyyMM}.log' -f (Get-Date))
$failed = 0

foreach ($folder in Get-ChildItem -Path $Root -Directory) {
    $db = $folder.Name
    # Last restored file, only for a database in the RESTORING or STANDBY state
    $query = "SET NOCOUNT ON; SELECT TOP (1) f.physical_device_name " +
             "FROM msdb.dbo.restorehistory AS h " +
             "JOIN msdb.dbo.backupset AS s ON s.backup_set_id = h.backup_set_id " +
             "JOIN msdb.dbo.backupmediafamily AS f ON f.media_set_id = s.media_set_id " +
             "JOIN sys.databases AS d ON d.name = h.destination_database_name " +
             "WHERE h.destination_database_name = N'$db' AND (d.state_desc = 'RESTORING' OR d.is_in_standby = 1) " +
             "ORDER BY h.restore_history_id DESC;"
    $last = sqlcmd -S $Instance -E -C -b -h -1 -W -w 1024 -Q $query | Where-Object { $_ }
    if ($LASTEXITCODE -ne 0) { $failed++; continue }
    if (-not $last) { continue }
    $lastName = Split-Path -Path $last -Leaf

    # Log backups newer than the last restored one, in name order
    $pending = Get-ChildItem -Path $folder.FullName -Filter '*_LOG.trn' | Where-Object Name -gt $lastName | Sort-Object Name
    foreach ($file in $pending) {
        $out = sqlcmd -S $Instance -E -C -b -Q "RESTORE LOG [$db] FROM DISK = N'$($file.FullName)' WITH NORECOVERY;"
        $out | Out-File -FilePath $log -Append -Encoding utf8
        if ($LASTEXITCODE -eq 0) { continue }
        # 4326: the backup ends before the database's current point, its changes are already applied
        if ($out -match 'Msg 4326,') { continue }
        $failed++
        break
    }
}
if ($failed -gt 0) { exit 1 }
exit 0

The script compares only the name of the last restored file, because the physical_device_name column may hold the path from the server where the backup was taken, and restores newer files in name order. SQL Server itself checks LSN continuity: a missing file stops the restore with error 4305. Error 4326 means a backup whose changes the database already contains, for example a log backup taken while the full backup used for initialisation was running, so the script skips it. The job is registered the same way as the log backup job, with -At '00:10', five minutes after the backup on srv-sql-01.

Instead of NORECOVERY you can use STANDBY = N'D:\SQLStandby\CrmDB.tuf'. The database is then readable between restores, but every restore needs exclusive access, so open report connections block it. The .tuf file must sit outside the folder covered by retention, because deleting it forces the database to be initialised again from scratch.

Failing over to srv-sql-02

For a planned failover, the last log backup on srv-sql-01 is taken with NORECOVERY, so the old database goes into the RESTORING state and no longer accepts changes:

-- Tail-log backup; the database goes into RESTORING and accepts no more changes
BACKUP LOG [CrmDB] TO DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_143000_LOG.trn' WITH NORECOVERY, CHECKSUM;

Running the log backup job by hand (Start-ScheduledTask) copies this file to the share, and running the restore job by hand on srv-sql-02 restores it. If srv-sql-01 has failed, recovery is run straight away on the files that reached the share:

-- End of the restore sequence: the database goes ONLINE and accepts no more log backups
RESTORE DATABASE [CrmDB] WITH RECOVERY;

# Removes the record with the srv-sql-01 address and adds one with the srv-sql-02 address, TTL 5 minutes
Remove-DnsServerResourceRecord -ZoneName corp.example.com -RRType A -Name sql-crm -RecordData 10.10.20.11 -ComputerName dc01.corp.example.com -Force
Add-DnsServerResourceRecordA -ZoneName corp.example.com -Name sql-crm -IPv4Address 10.10.20.12 -TimeToLive 00:05:00 -ComputerName dc01.corp.example.com

Clients remember the old address for the record’s TTL, so the TTL is lowered in advance, with the same commands and the 10.10.20.11 address. Windows logins are created on srv-sql-02 beforehand, and SQL logins with the same SID as on srv-sql-01, otherwise the database users are left without a login; the details are in the third part of this guide. The backup jobs on srv-sql-02 wait registered and disabled, with -Share pointing to \\srv-sql-01\SQLBackup\CRM, created with the same New-SmbShare command:

Disable-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - RESTORE'
Enable-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - FULL'
Enable-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - LOG'
# First full backup on the new server straight away
Start-ScheduledTask -TaskPath '\SQL\' -TaskName 'CRM - FULL'

How much data you can lose

Option	Maximum data loss	Time to restore	What has to be maintained
Nightly full backup only	a whole day of changes, up to 24 hours	restoring one .bak file, depending on database size and the speed of copying from the share	one job, retention, space on the share
Nightly full backup and a log backup every 15 minutes	up to 15 minutes plus the time to copy the file to the share	the full backup and every log backup after it, around 60 files restored one by one after a failure at 16:00	two jobs, databases in FULL, an unbroken log backup chain, space for log files
The same plus restores on srv-sql-02	as above	a quarter of an hour or so: the latest log backups, RECOVERY, the A record change	a second instance on the same version, the restore job, logins on both servers, a written failover procedure

The option is chosen according to the RPO and RTO agreed with the system owner. The third one turns restoring dozens of files under time pressure into a procedure that can be rehearsed.

Restore test

RESTORE VERIFYONLY checks that the backup is complete and readable, and its checksums, but not the structure of the data. Restoring every 15 minutes on srv-sql-02 confirms the continuity of the chain, but the database never goes through recovery there and nobody checks its consistency. A full test is a restore under a different name followed by a consistency check:

-- Logical file names needed for MOVE
RESTORE FILELISTONLY FROM DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_010000_FULL.bak';

RESTORE DATABASE [CrmDB_Test]
FROM DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_010000_FULL.bak'
WITH MOVE N'CrmDB' TO N'D:\SQLTest\CrmDB_Test.mdf',
     MOVE N'CrmDB_log' TO N'D:\SQLTest\CrmDB_Test_log.ldf',
     CHECKSUM, RECOVERY;

-- Consistency check; no messages means no errors
DBCC CHECKDB (N'CrmDB_Test') WITH NO_INFOMSGS;

DROP DATABASE [CrmDB_Test];

A point-in-time test adds log backups with NORECOVERY and the last one with STOPAT. How often to run such tests, who runs them and the protocol are described in A backup nobody has restored does not exist.

Check your own environment

The latest backup of each type for every database, with its location and checksum flag. A good result is type D no older than a day, type L no older than 30 minutes for databases in FULL, has_backup_checksums equal to 1 and a path under D:\SQLBackup. A path outside that folder for type L means a log backup taken outside the script, that is, a broken chain on the second server.

WITH last_backup AS (
    SELECT b.database_name, b.type, b.backup_finish_date, b.has_backup_checksums, f.physical_device_name,
           ROW_NUMBER() OVER (PARTITION BY b.database_name, b.type ORDER BY b.backup_finish_date DESC) AS rn
    FROM msdb.dbo.backupset AS b
    JOIN msdb.dbo.backupmediafamily AS f ON f.media_set_id = b.media_set_id
)
SELECT database_name, type, backup_finish_date,
       DATEDIFF(MINUTE, backup_finish_date, GETDATE()) AS minutes_ago,
       has_backup_checksums, physical_device_name
FROM last_backup
WHERE rn = 1
ORDER BY database_name, type;

The last run result of the jobs, on each server. A good result is LastTaskResult equal to 0, a LastRunTime no older than the job interval and a populated NextRunTime. A value of 1 is an error reported by the script and described in the log file in D:\SQLBackup\CRM, 267011 means a job that has not run yet, and a growing NumberOfMissedRuns counts runs the scheduler skipped.

Get-ScheduledTask -TaskPath '\SQL\' | Get-ScheduledTaskInfo |
    Select-Object TaskName, LastRunTime, LastTaskResult, NextRunTime, NumberOfMissedRuns

Restore history on srv-sql-02 alongside the time each backup was taken on srv-sql-01. A good result is type L entries every 15 minutes, a backup_finish_date on the latest entry no older than about 30 minutes, and the RESTORING state or is_in_standby equal to 1. A last restore several hours old means the chain has stalled, and the restore job’s log file shows the cause, most often error 4305.

SELECT TOP (10) h.destination_database_name, h.restore_date, h.restore_type,
       s.backup_finish_date, d.state_desc, d.is_in_standby
FROM msdb.dbo.restorehistory AS h
JOIN msdb.dbo.backupset AS s ON s.backup_set_id = h.backup_set_id
JOIN sys.databases AS d ON d.name = h.destination_database_name
ORDER BY h.restore_date DESC;

The header of a specific backup file, useful when looking for the missing link. The statement only reads the file and requires the CREATE DATABASE permission. BackupType 1 means a full backup and 2 a log backup, and HasBackupChecksums should be 1. The FirstLSN of a log backup must equal the LastLSN of the previous log backup of the same database; a difference means there was a backup between these files that is not in the folder.

RESTORE HEADERONLY FROM DISK = N'D:\SQLBackup\CRM\CrmDB\CrmDB_20261018_101500_LOG.trn';