Test-DbaLastBackup: prove your backups actually restore
A backup is only proven once it can be restored and the database is healthy afterward. The fact that the file exists and the backup job was green says little about that.
In our lab, one database had a damaged data page. The backup ran through, and so did the restore. Only DBCC CHECKDB on the restored copy found the damage. The faster check -VerifyOnly reported "Success" for the same backup.
dbatools has its own command for this test: Test-DbaLastBackup. This page shows what it does, where it restores to, which call worked in the lab, and where its results say less than their name promises. How to take backups and build a backup strategy is covered in our series Backup & Restore: basics.
In 60 seconds
- What the command does: It looks up the latest full, differential and log backups in the backup history, restores them as
dbatools-testrestore-<Name>, runsDBCC CHECKDBand deletes the copy again. - Without
-Destination, it restores on the source instance. Restore and CHECKDB then run on the production server, using its storage and load. Specify a test instance. - A different instance needs a shared path. If the backups are on a local path, the command skips the destination with
Restore not located on shared location. With-Pathpointing to a folder that both instances can see, it worked; on Windows, so did backups on a network share or-CopyFile. -VerifyOnlyis not enough. It only checks whether the backup is readable and does not find a damaged data page. Up to version 2.9.1 it also checks only the first file of a backup chain, and-Checksumdid not reach SQL Server.- Pass instance names, not connection objects. The command reconnects internally and loses certificate trust and credentials of a passed object. After a failed run, the test database stayed behind.
- Evaluate the result. When there is damage,
DbccResultcontains the message from SQL Server, not the word "Failure".
1 · Why restore plus CHECKDB
A backup can be unusable in three ways: the file is missing or unreadable, the chain of full, differential and log backups is broken, or the backed-up database was already damaged. Only the third case needs CHECKDB, and it is the most insidious. The backup looks flawless and still contains the damage.
BACKUP … WITH CHECKSUM checks the checksums of the data pages during the backup. In the lab, SQL Server did reject the backup of the damaged database this way:
BACKUP 'dbt_kaputt' detected an error on page (1:508) in file '/var/opt/mssql/data/dbt_kaputt.mdf'.
BACKUP DATABASE is terminating abnormally.Without CHECKSUM, which is the default unless you turn it on in the backup jobs or with the server option backup checksum default, the same backup ran through without a message. After that, only a restore followed by CHECKDB finds the damage.
2 · What Test-DbaLastBackup does, step by step
- It reads from
msdbthe latest full backup per database, plus the latest differential backup and the log backups after it. - It restores this chain as a new database
dbatools-testrestore-<Name>and renames the files in the process. - It runs
DBCC CHECKDBon the new database. - It deletes the test database and returns one result object per database.
The most important parameters (2.9.1 help text):
| Parameter | Effect |
|---|---|
-Destination, -DestinationSqlCredential | Target instance for the test; without it, the source instance |
-Path | Search a folder for backup files instead of the history; -Destination is then mandatory |
-Database, -ExcludeDatabase | Selection; without it, all databases |
-VerifyOnly | Only RESTORE VERIFYONLY, no restore, no CHECKDB |
-NoCheck, -NoDrop | Skip CHECKDB, or leave the test database in place |
-MaxSize | Skip databases above this size (MB) |
-MaxDop | Limit parallelism for CHECKDB |
-IgnoreDiffBackup, -IgnoreLogBackup | Do not use differential or log backups |
-DataDirectory, -LogDirectory | File location of the test database on the destination |
-CopyFile | Copy the backup files to the destination first (Windows, see section 4) |
3 · The call that worked in the lab
In the lab, each version had four test databases: dbt_ok with full, differential and log backup, dbt_nochk with a backup without checksum, dbt_bitflip with a backup file in which we overwrote 64 bytes, and dbt_kaputt with a deliberately damaged data page (created with the undocumented DBCC WRITEPAGE, only in a throwaway container). All four servers used a self-signed certificate. The working call looked like this:
$cred = Get-Credential sa
# Lab shortcut for self-signed certificates, this session only, connection stays encrypted (part 1)
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true
$r = Test-DbaLastBackup -SqlInstance sql2022 -SqlCredential $cred -Database dbt_ok, dbt_nochk, dbt_bitflip, dbt_kaputt
$r | Select-Object Database, RestoreResult, DbccResult, RestoreElapsed, DbccElapsedResult on SQL Server 2022 (excerpt):
Database RestoreResult DbccResult RestoreElapsed DbccElapsed
-------- ------------- ---------- -------------- -----------
dbt_ok Success Success 00:00:03 00:00:01
dbt_nochk Success Success 00:00:01 00:00:00
dbt_bitflip Success Success 00:00:01 00:00:00
dbt_kaputt Success Microsoft.Data.SqlClient.SqlException (0x80131904): Table error: … … …The beginning of DbccResult for dbt_kaputt read in full: Microsoft.Data.SqlClient.SqlException (0x80131904): Table error: Object ID 901578250, index ID 1, partition ID 72057594045726720, alloc unit ID 72057594052476928 (type In-row data), page (1:508). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4. Further down followed CHECKDB found 0 allocation errors and 3 consistency errors in database 'dbatools-testrestore-dbt_kaputt'.
All four versions behaved this way. For the four databases, one run took 13 to 17 seconds. The test databases were deleted again afterward.
dbt_ok: All three files were restored, CHECKDB found nothing.dbt_kaputt: The restore succeeded, CHECKDB found the damage. This is exactly what the command is for.dbt_nochk: A backup without a checksum is not an error; the content was fine.dbt_bitflip: Our pattern was an unlucky choice and canceled itself out in the checksum (side note in section 6). With random bytes, SQL Server detected the same kind of damage.
Evaluate instead of just looking. When there is damage, DbccResult contains the error message, not "Failure". So check for everything that is not Success, and list databases without a backup separately:
# Backed up, but restore or CHECKDB not clean
$r | Where-Object { $_.FileExists -and ($_.RestoreResult -ne 'Success' -or $_.DbccResult -ne 'Success') } |
Select-Object SourceServer, Database, RestoreResult, DbccResult
# No backup found: skipped, but a finding in itself
$r | Where-Object { -not $_.FileExists } | Select-Object SourceServer, Database, FileExists, RestoreResultIn the lab, the first query returned exactly dbt_kaputt with the CHECKDB message, the second the databases without a backup: master, model, msdb and a user database that had never been backed up, each with FileExists False and Skipped. Without -Database, the command checks all databases.
FileExists is a Boolean, not text. Our first version compared it with 'False'. PowerShell converts the text to $true, and the filter turned upside down: it reported the skipped databases and hid the damaged one.
4 · Where to restore to
The default is the source instance. Without -Destination, Test-DbaLastBackup restores on the server whose backups are being checked. Restore and CHECKDB then need storage, I/O and CPU there, for large databases over a longer time. For a regular run against production, the test belongs on a separate test instance.
A different instance needs a shared path. With a different destination, the command checks whether the path of the backup in the history starts with \\ (network share), http or s3. It skips local paths (source code 2.9.1, line 744). In the lab, the backups were on a volume that both instances saw under the same path. Even so:
Test-DbaLastBackup -SqlInstance sql2019 -SqlCredential $cred -Destination sql2022 -DestinationSqlCredential $cred -Database dbt_ok |
Select-Object Database, FileExists, RestoreResult, DbccResultDatabase FileExists RestoreResult DbccResult
-------- ---------- ------------- ----------
dbt_ok Skipped Restore not located on shared location SkippedIt worked with -Path. Instead of the history, the command then searches a folder. If that folder is on storage that the destination can see, it restores from there:
Test-DbaLastBackup -Path /var/opt/mssql/shared/sql2019 -Destination sql2022 -DestinationSqlCredential $cred -Database dbt_okIn the lab, restore and CHECKDB succeeded, with all three files. The SourceServer showed the host name of the container instead of sql2019, presumably because the command reads the source from the header of the backup file here.
Older destination version: A backup from SQL Server 2025 on a 2019 instance was cleanly rejected by SQL Server. The message was in RestoreResult: The database was backed up on a server running version 17.00.5005. That version is incompatible with this server, which is running version 15.00.4490. … So the test destination must be at least the version of the source; according to Microsoft, no backup can be restored on an earlier version.
On Windows: a share or -CopyFile. On a Windows server with two instances, we tested the same database against the second instance in three ways (Windows authentication, instance names):
| Backup located | Call | Result |
|---|---|---|
in the source's default backup folder (C:\Program Files\…\Backup) | -Destination PSG-WIN01\SQL2 | Skipped, Restore not located on shared location |
| same | plus -CopyFile | Success, Success |
on a share (\\PSG-WIN01\psgbackup\…) | -Destination PSG-WIN01\SQL2 | Success, Success |
With -CopyFile, the command copies the backup through the administrative shares of the Windows servers (source code 2.9.1, lines 692–707) into the destination's backup folder. In the lab, it then sat under C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2\MSSQL\Backup\. So it is only an option between Windows hosts, and it needs room there for another copy. For the share, the service accounts of both instances need rights. In the lab, we granted them to the virtual accounts NT Service\MSSQLSERVER and NT Service\MSSQL$SQL2, because both instances ran on the same server. Between two servers, you need domain accounts or the computer accounts; we did not build that. Since version 2.8.2 (May 2026), https and s3 paths also count as a shared location (#10362). We did not try that.
5 · Connection objects do not help here
In part 1 we recommend opening a connection with Connect-DbaInstance and passing the object on. With Test-DbaLastBackup, that went wrong in the lab. The command reconnects internally by instance name (source code 2.9.1, lines 549 and 577 for source and destination, line 876 before CHECKDB). It only takes over explicitly passed -SqlCredential and -DestinationSqlCredential.
- First attempt with connection objects from
Connect-DbaInstance -TrustServerCertificate: The trust was lost, certificate warnings appeared and there were no results. - Second attempt, additionally with trust through a session configuration: Now the credentials were missing. dbatools tried a Windows login, which does not exist in the container:
The target principal name is incorrect. Cannot generate SSPI context. - In both attempts, a run failed after the restore, and the test database
dbatools-testrestore-dbt_okstayed behind on the destination. - On Windows, with Windows authentication and a connection object from
Connect-DbaInstance -TrustServerCertificate, the very first internal connection failed on the certificate:The certificate chain was issued by an authority that is not trusted. There were no results and no leftovers. Authentication would not have been a problem here; the trust was lost all the same.
What works: Instance names plus -SqlCredential and -DestinationSqlCredential, trust only through a session configuration (section 3). As of Nov 16, 2026, we found no report of this in the project.
Cleanup is part of it. If you run the command regularly, you should look for leftovers afterward:
-- Find leftovers of Test-DbaLastBackup on the destination instance (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT name, create_date, state_descFROM sys.databasesWHERE name LIKE N'dbatools-testrestore-%'OPTION (MAXDOP 1);Remove any leftovers you find after you have checked that no run is still active, for example with Remove-DbaDatabase and -WhatIf first. In the lab, for a leftover we had kept on purpose, -WhatIf reported What if: Performing the operation "KillDatabase" on target "[dbatools-testrestore-dbt_ok] on [sql2022]". The call without -WhatIf returned Status Dropped, and afterwards the query found nothing.
6 · -VerifyOnly and -Checksum: weaker than they sound
With -VerifyOnly, the command only runs RESTORE VERIFYONLY. SQL Server reads the backup and checks whether it is complete and readable. Nothing is restored, and CHECKDB does not run. In the lab, -VerifyOnly therefore reported Success for the backup with the damaged data page, on all four versions.
Two further limitations apply up to and including version 2.9.1:
- Only the first file.
-VerifyOnlychecks only the first backup of a chain, so fordbt_okonly the full backup. The maintainers corrected this in the development version (#10791); the fix had not been released on Nov 17, 2026; the latest version was still 2.9.1. -Checksumdoes not arrive. According to the help,-VerifyOnly -Checksumshould fail if a backup contains no checksums. In the lab it reportedSuccessfordbt_nochkon all versions. A capture with Extended Events showed what dbatools sends to SQL Server:
RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/data/nochk.bak' WITH FILE = 1, NOUNLOADThere is no CHECKSUM in it. The same check run directly in SQL Server with WITH CHECKSUM aborts as the help promises:
Msg 3187, Level 16, State 1, Server 1cf80207bb49, Line 1
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.
Msg 3013, Level 16, State 1, Server 1cf80207bb49, Line 1
VERIFY DATABASE is terminating abnormally.As of Nov 16, 2026, this finding had not been reported in the project. Until then, you can see directly in the history whether your backups carry checksums:
-- Backups of the last 7 days without a checksum (read-only)-- MAXDOP 1: runs serially and does not occupy further cores of the applicationSELECT b.database_name, b.type, -- D = full, I = differential, L = log b.backup_finish_date, b.has_backup_checksums, mf.physical_device_nameFROM msdb.dbo.backupset AS bJOIN msdb.dbo.backupmediafamily AS mf ON mf.media_set_id = b.media_set_idWHERE b.backup_finish_date >= DATEADD(DAY, -7, SYSDATETIME()) AND b.has_backup_checksums = 0ORDER BY b.backup_finish_date DESCOPTION (MAXDOP 1);Side note on the checksum. Our test backup dbt_bitflip with 64 overwritten bytes passed all checks, including CHECKDB, and the change was in the database afterward. The cause lies in SQL Server, not in dbatools. The checksum of a data page combines its 4-byte words with XOR. If an even number of words in the same 512-byte section of a page change in exactly the same way, the changes cancel each other out. Our pattern, uniform spaces overwritten with 0xFF, hit exactly this case. SQL Server 2022 detected single bytes, random content and odd patterns immediately. We recomputed the method on 3,439 pages; it is described only in secondary sources. For everyday work this means: the page checksum is a fast detector for typical storage and transfer errors, but not a protection against targeted modification.
7 · Testing regularly
- On a test instance with at least the version of the source, its own storage and access to the backups (network share, URL or shared path with
-Path). - With limits:
-MaxSizeskips databases that are too large for the test destination,-MaxDoplimits CHECKDB. Skipped databases belong in the report, so that they do not go unnoticed and never get tested. - Scheduled and logged: as a task on a management machine, as an Agent job with a PowerShell step on Windows, or as a pipeline job. On Linux, SQL Server Agent cannot run PowerShell steps (Microsoft lists the PowerShell and CmdExec subsystems as unsupported there); there the command runs from a separate machine or container. The result objects can be written to CSV or to a table.
- With a cleanup step at the end (section 5) and an evaluation that reports everything that is not
Success(section 3).
What we measured and what we did not
- Measured (Nov 16, 2026): dbatools 2.9.1 under PowerShell 7.6.8 in a Linux container against SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9 in Linux containers. The backups were on a shared Docker volume. The databases were small (4 to 40 MB); the run times are lab values and say nothing about large databases. The cross-checks on checksums and on the
RESTOREcommand that was sent ran on SQL Server 2022. - Measured on Windows (Nov 17, 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; one small database (about 10 MB) with a local backup, a share and
-CopyFile, plus the connection object. The VM had one vCPU; run times of 3 to 7 minutes per test are therefore not meaningful. - Not measured: two separate Windows servers (share rights, administrative shares across the network), Agent jobs with a PowerShell step, backups on Azure Blob Storage or S3, encrypted backups, large databases and the load of a test on a production server.
Further reading
- dbatools: Test-DbaLastBackup (help with examples).
- Microsoft Learn: RESTORE VERIFYONLY, Possible media errors during backup and restore, backup checksum default, backupset (
has_backup_checksums), RESTORE, compatibility support. - At PSG: Backup & Restore: basics and Advanced, dbatools overview, Part 1: Install and connect securely.