A successful backup job proves that a backup operation finished. Weekly restore tests provide stronger evidence by recovering the database and checking the recovered copy.
Define What Weekly Restore Tests Must Prove
The test should select the latest completed full backup, read its media, and restore every database file to isolated storage. Then it runs a full consistency check and retains an operating result. It does not prove every point-in-time chain unless log and differential recovery are tested too. Give those additional scenarios their own scheduled exercises.
Use a dedicated test instance with adequate capacity and compatible engine version. Keep restored Agent jobs, outbound connections, and application routing separate from production. Database encryption can require protected certificates on that server. I define those prerequisites before automating the restore, because a permission or certificate failure is a real failed test rather than a reason to skip the database silently.
Rotate every required database through a queue and review the maximum age of its last successful test. One database each week is insufficient when the queue takes longer than the accepted coverage window. Adjust the schedule or process multiple entries within a bounded window. The queue should not award perfect attendance to the smallest database.
Collect the Latest Full Backup on the Source
Run the inventory query on the source instance, then securely copy the selected media and inventory to the test server through the approved backup process. The destination's local msdb does not automatically know the source's latest backups. This query returns the latest full backup even when its media layout needs additional handling.
DECLARE @DatabaseName sysname=N'SalesDB';
SELECT TOP (1) b.database_name,b.backup_set_id,b.position,
b.backup_finish_date,b.is_damaged,b.has_backup_checksums,
COUNT(m.family_sequence_number) AS MediaFamilies,
MIN(m.physical_device_name) AS ExampleMediaPath
FROM msdb.dbo.backupset AS b
JOIN msdb.dbo.backupmediafamily AS m ON m.media_set_id=b.media_set_id
WHERE b.database_name=@DatabaseName AND b.type='D'
AND b.backup_finish_date IS NOT NULL
GROUP BY b.database_name,b.backup_set_id,b.position,
b.backup_finish_date,b.is_damaged,b.has_backup_checksums
ORDER BY b.backup_finish_date DESC,b.backup_set_id DESC;For the single-file runner below, the collector must reject a damaged backup or multiple media families and record an actionable inventory failure. Do not silently choose an older single-file backup when the latest backup is striped. Extend the reviewed runner to include every family before accepting that layout. Preserve the backup-set position, completion time, checksum state, and source identity with the transferred file.
Log Restore Tests Outside the Restored Database
Create a monitoring database named RestoreMonitor on the isolated server first. Its queue is populated by the approved collector after the file transfer and prerequisite checks. For restore tests, the result table remains available when the restored test database is removed. Limit write access to the collector and runner identities.
USE RestoreMonitor;
GO
CREATE TABLE dbo.RestoreQueue
(
SourceDatabase sysname PRIMARY KEY,
BackupPath nvarchar(4000) NOT NULL,
BackupPosition int NOT NULL CHECK(BackupPosition>0),
LastAttemptUTC datetime2(0) NULL,
Enabled bit NOT NULL DEFAULT(1)
);
CREATE TABLE dbo.RestoreRun
(
RunID uniqueidentifier PRIMARY KEY,
SourceDatabase sysname NOT NULL,
TestDatabase sysname NOT NULL,
StartedUTC datetime2(0) NOT NULL,
FinishedUTC datetime2(0) NULL,
Outcome nvarchar(20) NOT NULL,
Detail nvarchar(2000) NULL
);
GORetain the source inventory alongside each run in the production version of this schema. That makes the result attributable to a particular backup rather than a changing queue entry. Also record file size, available destination capacity, engine build, and CHECKDB completion. A result that lacks the selected backup's identity is harder to interpret after the next transfer.
Restore All Ordinary Files to New Paths
Save the following PowerShell script as C:\SqlMaintenance\RestoreTests.ps1 after reviewing the connection and storage directories. It uses Windows integrated authentication. The account needs the approved restore, metadata, consistency-check, and monitoring permissions. The directories must already exist and be writable by the SQL Server service account.
# PowerShell
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = 'Server=RestoreServer;Database=RestoreMonitor;Integrated Security=True;Encrypt=True;TrustServerCertificate=False'
$Connection.Open()
function Read-Table([string]$Text) {
$Command = $Connection.CreateCommand()
$Command.CommandText = $Text
$Command.CommandTimeout = 1800
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
$Table = New-Object System.Data.DataTable
[void]$Adapter.Fill($Table)
return ,$Table
}
function Execute-Sql([string]$Text) {
$Command = $Connection.CreateCommand()
$Command.CommandText = $Text
$Command.CommandTimeout = 1800
[void]$Command.ExecuteNonQuery()
}
function Sql-Literal([string]$Value) { return "N'" + $Value.Replace("'","''") + "'" }
function Sql-Identifier([string]$Value) { return '[' + $Value.Replace(']',']]') + ']' }
try {
$Queue = Read-Table 'SELECT TOP (1) SourceDatabase,BackupPath,BackupPosition FROM dbo.RestoreQueue WHERE Enabled=1 ORDER BY LastAttemptUTC,SourceDatabase;'
if ($Queue.Rows.Count -eq 0) { throw 'No enabled restore-test entry exists.' }
$Entry = $Queue.Rows[0]
$RunID = [guid]::NewGuid().ToString()
$TestName = 'RestoreTest_' + $RunID.Replace('-','')
$TestIdentifier = Sql-Identifier $TestName
$SourceLiteral = Sql-Literal ([string]$Entry.SourceDatabase)
$FileLiteral = Sql-Literal ([string]$Entry.BackupPath)
$Position = [int]$Entry.BackupPosition
Execute-Sql "UPDATE dbo.RestoreQueue SET LastAttemptUTC=SYSUTCDATETIME() WHERE SourceDatabase=$SourceLiteral; INSERT dbo.RestoreRun VALUES('$RunID',$SourceLiteral,N'$TestName',SYSUTCDATETIME(),NULL,N'Running',NULL);"
try {
$Headers = Read-Table "RESTORE HEADERONLY FROM DISK=$FileLiteral;"
$ChosenHeader = @($Headers.Rows | Where-Object { [int]$_.Position -eq $Position })
if ($ChosenHeader.Count -ne 1 -or [string]$ChosenHeader[0].DatabaseName -ne [string]$Entry.SourceDatabase) { throw 'The backup header does not match the selected queue entry.' }
if ([bool]$ChosenHeader[0].IsDamaged -or -not [bool]$ChosenHeader[0].HasBackupChecksums) { throw 'This runner requires an undamaged backup with backup checksums.' }
$Files = Read-Table "RESTORE FILELISTONLY FROM DISK=$FileLiteral WITH FILE=$Position;"
$Moves = @()
$FileNumber = 0
foreach ($File in $Files.Rows) {
$FileNumber++
if ([string]$File.Type -notin @('D','L')) { throw 'Review the unsupported file type before testing this backup.' }
$Extension = if ([string]$File.Type -eq 'L') { '.ldf' } else { '.ndf' }
$Target = 'C:\RestoreTestData\' + $TestName + '_' + $FileNumber + $Extension
$Moves += 'MOVE ' + (Sql-Literal ([string]$File.LogicalName)) + ' TO ' + (Sql-Literal $Target)
}
if ($Moves.Count -eq 0) { throw 'No restore files were identified.' }
$MoveClause = $Moves -join ','
Execute-Sql "RESTORE DATABASE $TestIdentifier FROM DISK=$FileLiteral WITH FILE=$Position,$MoveClause,RECOVERY,CHECKSUM;"
$Check = Read-Table "DBCC CHECKDB($TestIdentifier) WITH TABLERESULTS,NO_INFOMSGS;"
if ($Check.Rows.Count -gt 0) { throw 'CHECKDB returned messages requiring review.' }
Execute-Sql "DROP DATABASE $TestIdentifier;"
Execute-Sql "UPDATE dbo.RestoreRun SET FinishedUTC=SYSUTCDATETIME(),Outcome=N'Passed',Detail=N'Restored, checked and removed.' WHERE RunID='$RunID';"
} catch {
$Detail = Sql-Literal ($_.Exception.Message.Substring(0,[Math]::Min(2000,$_.Exception.Message.Length)))
Execute-Sql "UPDATE dbo.RestoreRun SET FinishedUTC=SYSUTCDATETIME(),Outcome=N'Failed',Detail=$Detail WHERE RunID='$RunID';"
throw
}
} finally {
$Connection.Dispose()
}The unique database name and new file paths avoid overwriting an existing recovery copy. Failure preserves the test database for investigation when it exists. Review and remove failed copies through a separate controlled cleanup process. A client timeout can leave recovery work needing inspection; do not immediately issue a competing restore or force users out.
The script requires one runner at a time. For concurrent workers, add a transactional queue claim and a lease rather than letting workers select the same row. It also needs monitoring for stale Running entries if the host stops before the catch block records the error. Automation cannot report an exception after its own power disappears.
Verify the Selected Media and Test Boundaries
The runner checks the selected backup header against the queue database and requires backup checksums. This prevents a transferred file with an unexpected database name from receiving a misleading result. The collector must also verify source freshness and the full-backup type before enabling the entry. A valid restore of stale media is useful evidence about that media, but it does not satisfy a test of the latest backup.
The example supports ordinary data and log files. Specialized file types require a reviewed extension with appropriate directory handling and destination support. Mark unsupported databases as coverage gaps until that extension works. Keep their records in the inventory so a report cannot improve merely by hiding a difficult case.
Set the command timeout and scheduling window from measured restore and CHECKDB durations on the isolated hardware. Include file-transfer time when evaluating the complete recovery process. This weekly check measures backup usability rather than the whole business recovery objective. An accepted service recovery test still needs application authentication, representative operations, and dependency validation.
Schedule Restore Tests as a Weekly Agent Step
Create a reviewed PowerShell Agent step that runs the saved local script under the appropriate approved execution identity. The example creates a Monday schedule and attaches it to the job. Configure failure notifications and the necessary proxy permissions for the actual deployment.
USE msdb;
GO
EXEC dbo.sp_add_job @job_name=N'Weekly Restore Test';
EXEC dbo.sp_add_jobstep @job_name=N'Weekly Restore Test',
@step_name=N'Test next database',@subsystem=N'PowerShell',
@command=N'& ''C:\SqlMaintenance\RestoreTests.ps1''';
EXEC dbo.sp_add_schedule @schedule_name=N'Monday Restore Test',
@freq_type=8,@freq_interval=2,@freq_recurrence_factor=1,
@active_start_time=90000;
EXEC dbo.sp_attach_schedule @job_name=N'Weekly Restore Test',
@schedule_name=N'Monday Restore Test';
EXEC dbo.sp_add_jobserver @job_name=N'Weekly Restore Test';
GOI test the scheduled identity as well as the interactive script. File access, certificate validation, and database rights can differ between those contexts. Trigger a controlled failure and confirm that the job and durable result both indicate failure. A green job status without an attributable consistency result is insufficient.
Review Coverage and Act on Failures
Which database has waited longest for a successful restore test? Review that alongside failed, stale, and missing runs. Investigate unavailable media, damaged backups, capacity problems, and consistency findings with their owners. Do not delete the failure record when the next run succeeds.
Restore tests turn backup confidence into observable recovery evidence. Expand this full-backup test with differential, log-chain, encryption, and application checks according to the service's recovery objectives. Keep the last successful result, the current gap, and the next required test visible together.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Check Backup Reliability.
A restore test is not another backup-status check, it is evidence that selected recovery media can produce a checked database.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
