Production users should not compete with every integrity-check read when another server can do the work. Running CHECKDB on a restored copy moves that workload to an isolated destination. The result belongs to the restored backup, with a clear cutoff in time.
Identify the Backup You Are Actually Testing
Choose the latest intended full backup from the production backup process. Record its database, completion time, backup-set position, and storage location. A filename containing the word latest does not establish any of those facts.
If the backup uses multiple striped files, retain every required file. A backup media file can also contain several backup sets. FILE identifies the set position, so inspect the header before choosing a restore command.
I start these checks with backup identity rather than the CHECKDB command. I also keep the restore evidence beside the integrity-check evidence. A clean check of the wrong database is an impressively tidy mistake.
The destination needs a supported engine version and enough storage for restored files and checking activity. A backup from a newer SQL Server cannot be restored to an older major version. Encryption also requires the appropriate keys or certificates on the destination.
Inspect the Media and Plan File Locations
The following commands read a backup accessible to the destination SQL Server service account. Replace the example path with your selected backup file. SQL Server interprets that path on the server, not on the computer running SSMS.
RESTORE HEADERONLY
FROM DISK = N'C:\SqlBackups\SourceDb_full.bak';
RESTORE FILELISTONLY
FROM DISK = N'C:\SqlBackups\SourceDb_full.bak'
WITH FILE = 1;
RESTORE VERIFYONLY
FROM DISK = N'C:\SqlBackups\SourceDb_full.bak'
WITH FILE = 1, CHECKSUM;Choose FILE from the header result rather than assuming the first position is correct. FILELISTONLY supplies logical names for each restored file. Every data, log, and special storage container needs an intentional destination mapping.
VERIFYONLY checks whether the backup set is complete and readable, with available checksum validation. It does not perform the logical consistency checks of a recovered database. Successful verification therefore does not replace restoration followed by CHECKDB.
Use separate destination paths that cannot overlap production files. Create required folders and grant the destination service account the necessary access. Confirm free space from actual file sizes rather than estimating from compressed backup size alone.
Restore under a Separate Database Name
The example assumes FILELISTONLY returned logical names SourceDb and SourceDb_log. Replace those names with the actual results and add MOVE clauses for additional files. Do not omit an extra file merely because the simplified example contains two.
USE master;
GO
IF DB_ID(N'SourceDbCheckCopy') IS NOT NULL
THROW 51000, 'Choose an unused destination database name.', 1;
RESTORE DATABASE SourceDbCheckCopy
FROM DISK = N'C:\SqlBackups\SourceDb_full.bak'
WITH FILE = 1,
MOVE N'SourceDb' TO N'C:\SqlData\SourceDbCheckCopy.mdf',
MOVE N'SourceDb_log' TO N'C:\SqlData\SourceDbCheckCopy_log.ldf',
RECOVERY, CHECKSUM;
GO
SELECT name, state_desc
FROM sys.databases
WHERE name = N'SourceDbCheckCopy';There is no REPLACE option in this restore. The unused-name check and separate paths reduce accidental replacement risk. Verify the destination server identity before running it, because a good script cannot correct a mistaken connection.
A full-backup-only check validates the recovered state represented by that backup. To test a later recoverable point, restore the required differential and log chain in order. Keep NORECOVERY until the final intended restore, then recover the database.
For this article, the latest full backup is the defined test input. Record its time boundary explicitly. Restore completion also provides practical recovery evidence that a media verification alone cannot supply.
Run CHECKDB on a Restored Copy and Keep Every Message
Run CHECKDB after recovery finishes and the destination database is online. NO_INFOMSGS removes routine informational output, while ALL_ERRORMSGS preserves reported errors. Do not add a repair option to a routine verification job.
DBCC CHECKDB (N'SourceDbCheckCopy')
WITH NO_INFOMSGS, ALL_ERRORMSGS;A full check covers more than physical page verification. PHYSICAL_ONLY reduces the checking scope and does not replace this full-check contract. Use additional supported options only when their coverage and cost belong in your integrity policy.
Some object types and checks have documented exclusions or require additional options. CHECKDB is not a business-rule validator for invoices or application calculations. Its successful completion establishes the consistency covered by its checks, not correctness of every business value.
Capture errors, cancellation, connection loss, and successful completion separately. An empty output file after an interrupted process proves nothing. Your automation must know that the command completed before labeling the restored copy checked.
Log the Command Outcome on Windows
The following PowerShell example uses the installed SQL Server sqlcmd utility and integrated authentication. Set the server and log folder for your verification environment. The ODBC 18 sqlcmd encrypts connections by default, so the client must trust the server certificate. It runs only the check against the already restored copy.
# PowerShell
$checkServer = 'SERVER\INSTANCE'
$logFolder = 'C:\SqlCheckLogs'
New-Item -ItemType Directory -Path $logFolder -Force | Out-Null
$stamp = Get-Date -Format 'yyyyMMdd-HHmmss-fff'
$outputPath = Join-Path $logFolder "checkdb-$stamp.txt"
$started = [DateTime]::UtcNow
& sqlcmd -S $checkServer -E -d master -b -l 30 -t 0 `
-Q "DBCC CHECKDB (N'SourceDbCheckCopy') WITH NO_INFOMSGS, ALL_ERRORMSGS;" `
2>&1 | Out-File -LiteralPath $outputPath -Encoding utf8
$checkExit = $LASTEXITCODE
[pscustomobject]@{
Database = 'SourceDbCheckCopy'
StartedUtc = $started.ToString('o')
FinishedUtc = [DateTime]::UtcNow.ToString('o')
ExitCode = $checkExit
OutputFile = $outputPath
} | Export-Csv -LiteralPath (Join-Path $logFolder 'check-history.csv') `
-NoTypeInformation -Append
if ($checkExit -ne 0) {
throw "Integrity check failed. Review $outputPath"
}The -b option makes qualifying SQL errors produce a failing exit status. Retain the actual output as well as that status. A launch failure or authentication failure must remain a failed attempt, not a clean database result.
This example allows the command to run without a query timeout. Your scheduler still needs an operational time budget and alerting for overdue work. Record cancellations explicitly if that budget ends an incomplete check.
State What CHECKDB on a Restored Copy Proves
A successful restore and full check support a precise statement about this backup's recovered database state. They do not certify every page currently stored on production. Production storage can develop a problem after the backup captured its pages.
I review the age of the last successful restored-copy check alongside production storage alerts. I also keep some direct production checking in the wider integrity plan. Moving heavy checks elsewhere changes coverage and timing responsibilities rather than abolishing them.
If the check finds corruption, retain the messages and investigate the source and destination storage paths. Compare other known-good backups without overwriting your evidence. Do not jump straight to data-loss repair because the failed object happens to be a disposable copy.
How old is the recoverable state you last restored and checked? That is a more useful operational question than whether a green job ran last night. Keep the backup identifier with the answer so another DBA can repeat the test.
Retain the original backup media until the verification result is reviewed. A failed destination restore deserves investigation before that backup becomes the next discarded file. Store the chosen backup-set position with the output so the test can be repeated precisely.
Use CHECKDB on a restored copy as a scheduled recovery exercise with a named owner. Retain failed attempts alongside successful ones. A history containing only green outcomes hides authentication failures and missed checks.
A recent CHECKDB on a restored copy also helps establish which recovery point you have tested. It does not select that point for you. Match the retained media to the recovery objective before an incident forces the decision.
Related reading on this blog: Splitting DBCC CHECKDB Across the Week for a Large Database and SQL SERVER 2022: Last Valid Restore Time: Improved Backup Metadata.
A clean restored copy is not today's production guarantee, it is verified evidence for one recoverable state.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
