Schedule a Nightly SQL Server Backup with a PowerShell Job

Before you run this

This guide sets up an unattended nightly full database backup. A short PowerShell script calls Backup-SqlDatabase to write a .bak file to a folder, and Windows Task Scheduler runs that script on a schedule under a service account.

What it changes: it creates a scheduled task and writes backup files to a folder you choose. The backup itself is non-destructive to your database — Backup-SqlDatabase reads the data, it does not alter it. The two things that do change state are: the scheduled task you register (removable), and the disk filling with .bak files over time (which is why I include an optional, opt-in cleanup step you must read carefully before enabling).

Privileges you need:

An elevated PowerShell session (Run as Administrator) to install the module system-wide and to register the scheduled task.
The SqlServer PowerShell module from the PowerShell Gallery. It is not built in.
The account the task runs as needs a SQL Server login that is a member of the db_backupoperator database role (or sysadmin), plus write permission on the backup folder at the Windows level.

Test first. Read the script before you run it. Run the backup command by hand once, against a test database on a non-production instance, and confirm a file lands where you expect. Do not register a scheduled task on a production box until the manual run works cleanly. The optional cleanup step deletes files — leave it disabled until you have watched it list (via -WhatIf) exactly which files it would remove.

What I'm assuming

Windows Server 2019 or 2022, standalone (not a Failover Cluster or Availability Group — those have their own backup considerations).
SQL Server 2017 or later, one instance, using Windows authentication.
PowerShell 5.1 (the version shipped with Windows Server).
A dedicated backup account, DOMAIN\svc_sqlbackup, exists. If you're standalone rather than domain-joined, use a local account instead and adjust the name.

Where your setup differs, the usual variables are the instance name (a default instance is just the hostname; a named instance is HOST\INSTANCE) and the backup path.

Step 1 — Install the SqlServer module

From an elevated PowerShell prompt:

# Installs the module for all users; needs admin. Use -Scope CurrentUser if you can't elevate.
Install-Module -Name SqlServer -Scope AllUsers

If this is the machine's first PowerShell Gallery install it may prompt to trust the repository and to install the NuGet provider — that's expected. Confirm it landed:

Get-Module -ListAvailable SqlServer | Select-Object Name, Version

Step 2 — Write the backup script

Save this as C:\Scripts\Backup-MyDatabase.ps1. Replace the three values at the top with your own. C:\Scripts and D:\SQLBackups are placeholders — substitute your real paths, and make sure the backup folder already exists.

# Backup-MyDatabase.ps1 — full backup of one database to a timestamped file.

Import-Module SqlServer

# --- edit these three ---
$instance  = 'SQLHOST'            # default instance = hostname; named = 'SQLHOST\INSTANCE'
$database  = 'YourDatabase'       # the database to back up
$backupDir = 'D:\SQLBackups'      # must exist, and the task account must be able to write here
# ------------------------

# Unique filename per run so we never overwrite an earlier backup.
$timestamp  = Get-Date -Format 'yyyyMMdd_HHmmss'
$backupFile = Join-Path $backupDir ("{0}_{1}.bak" -f $database, $timestamp)

# Keep a simple transcript so a failed scheduled run leaves a trail.
$logFile = Join-Path $backupDir 'backup-log.txt'

try {
    "$([DateTime]::Now) : starting backup of $database to $backupFile" |
        Out-File -FilePath $logFile -Append

    Backup-SqlDatabase `
        -ServerInstance   $instance `
        -Database         $database `
        -BackupFile       $backupFile `
        -CompressionOption On `
        -Checksum

    "$([DateTime]::Now) : SUCCESS" | Out-File -FilePath $logFile -Append
}
catch {
    "$([DateTime]::Now) : FAILED — $($_.Exception.Message)" |
        Out-File -FilePath $logFile -Append
    throw   # re-throw so Task Scheduler records a non-zero result
}

Notes on the non-obvious parts:

-CompressionOption On requests backup compression. It's supported on Standard and Enterprise editions; on editions that don't support it the option is simply ignored, so it's safe to leave in.
-Checksum asks SQL Server to compute page checksums as it writes, so a corrupt page is more likely to be caught at backup time.
I do not use -Initialize (which overwrites a backup file's contents), because each run writes a new, uniquely named file. That's deliberate — appending or overwriting into one file makes accidental loss easier.

Run it by hand once before scheduling anything:

& C:\Scripts\Backup-MyDatabase.ps1

Then confirm the .bak file exists and the log says SUCCESS.

Step 3 — Register the scheduled task

Still in an elevated session. This registers a daily task that runs the script under your service account. Replace the account name, and choose your own run time.

$action = New-ScheduledTaskAction `
    -Execute 'powershell.exe' `
    -Argument '-NoProfile -ExecutionPolicy Bypass -File "C:\Scripts\Backup-MyDatabase.ps1"'

$trigger = New-ScheduledTaskTrigger -Daily -At 2:00AM   # pick a quiet hour

Register-ScheduledTask `
    -TaskName    'SQL Backup - MyDatabase' `
    -Action      $action `
    -Trigger     $trigger `
    -User        'DOMAIN\svc_sqlbackup' `
    -RunLevel    Highest `
    -Description 'Nightly full backup of MyDatabase via PowerShell.'

You'll be prompted for the account's password so Task Scheduler can store it. A Group Managed Service Account (gMSA) avoids storing a password at all; that's the other common approach and worth reading up on if you have an AD to support it, but it's beyond this guide.

-ExecutionPolicy Bypass applies only to this one invocation of powershell.exe; it doesn't change the machine-wide policy.

Step 4 — Verify it works

Kick the task off manually rather than waiting for 2 a.m.:

Start-ScheduledTask -TaskName 'SQL Backup - MyDatabase'

# 0 means the last run succeeded.
Get-ScheduledTaskInfo -TaskName 'SQL Backup - MyDatabase' |
    Select-Object LastRunTime, LastTaskResult

Confirm a fresh .bak file appeared in your backup folder and check backup-log.txt.

Then prove the backup is actually restorable — a backup you can't restore isn't a backup. This reads the file's structure without touching your database:

Invoke-Sqlcmd -ServerInstance 'SQLHOST' -Query @"
RESTORE VERIFYONLY FROM DISK = N'D:\SQLBackups\YourDatabase_YYYYMMDD_HHMMSS.bak'
"@

A clean run reports the backup set is valid. For real confidence, periodically do a full restore to a scratch instance with Restore-SqlDatabase — verifying and restoring are not the same thing.

Optional — pruning old backups

Only add this once you're comfortable, and run it with -WhatIf first so it shows what it would delete without deleting anything. This is the destructive part; there is no undo once a .bak is gone.

# Preview what would be removed (older than 14 days). Nothing is deleted with -WhatIf.
Get-ChildItem 'D:\SQLBackups' -Filter '*.bak' |
    Where-Object LastWriteTime -lt (Get-Date).AddDays(-14) |
    Remove-Item -WhatIf

When the preview is exactly right, remove -WhatIf and add it to the end of the backup script. Set the retention window to match your recovery needs, not mine.

Undo

To remove the schedule (this does not touch existing backup files):

Unregister-ScheduledTask -TaskName 'SQL Backup - MyDatabase' -Confirm:$false

Delete the script and the .bak files by hand if you're tearing it down completely.

For exact parameter details, see Microsoft Learn for Backup-SqlDatabase, Register-ScheduledTask, and Invoke-Sqlcmd, and the SQL Server documentation on RESTORE VERIFYONLY. When your requirements grow — transaction-log backups, differentials, multiple databases, alerting — that's the point at which SQL Server Agent jobs become the more natural home for backups than Task Scheduler.