If you haven't done any restoration of your backups, you cannot count it as your retrieval strategy. It's simply a case of gambling. The backup job completes successfully once the data is written – how it will do after that is a different story. Corrupted backups, broken chains, and retrieval times that keep increasing exponentially remain camouflaged behind the success signs until you face the hard truth at 2 a.m.
It’s clear what people should do - restore their backups regularly, check them with CHECKDB, and time against RTO. However, no one enjoys doing it as the process is quite laborious. Let’s automate the whole thing.
This article will provide the readers with a complete T-SQL solution. The solution will be executed on a restore server, will identify the chain of backups on a production server, restore it, verify the outcome using CHECKDB, and log the results. Everything mentioned in the article concerns SQL Server 2016+.
The wrinkle nobody warns you about
Here’s what goes wrong with many attempts at "just restore it on another box": backup history lives in msdb on the source server. The msdb on your restore instance knows only about backups done by that instance. Point this contraption at a new restore box and ask it "what is the newest backup chain for Orders?" and it says "I don’t know, because the answer is in production and not here."
Thus, the design has to combine two operations:
First, find out the history remotely by querying the production server's msdb through the linked server.
Second, execute restore locally by executing the real RESTORE and CHECKDB on the scratch instance, using backup files found at a path accessible to the restore instance.
That split keeps the heavy restore + CHECKDB work away from production and allows production to use the authoritative backup history. However, it requires a linked server, and it requires availability of backup files accessible from the restore box. I will discuss this last point in detail, but this is the point that is the problem for many people.
One safety rule first
This command allows the restoration of a damaged database in an instance of any type. In order to do so, the instance should never be a live production one; all operations should be done on a separate instance for recovery and checking. The process is heavy-duty due to the fact of restoring combined with a full CHECKDB check. There is a safeguard that makes sure that the command is executed properly and reliably as it is supposed to have the @IConfirmThisIsARestoreInstance = 1 statement in the command. Thus, the database is restored under a temporary name and cannot interfere with anything. However, commandeering the process remains an individual's responsibility and can be performed on any computing device available for public use.
Step 1 setting, output of the command, and paths
It contains three main tables. The first one stores configuration information about the process of validation, such as the required RTO and the database location. The second one keeps log information about the data recovery process. The last one works to indicate the locations of all recoverable files.
CREATE TABLE dbo.RestoreVerifyConfig
(
DatabaseName SYSNAME NOT NULL PRIMARY KEY,
SourceServer SYSNAME NULL,
SourceLinkedServer SYSNAME NOT NULL, -- linked server to the source msdb
RTOTargetSeconds INT NOT NULL DEFAULT 1800,
RestoreDataPath NVARCHAR(260) NOT NULL, -- scratch instance's data drive
RestoreLogPath NVARCHAR(260) NOT NULL, -- scratch instance's log drive
RunCheckDB BIT NOT NULL DEFAULT 1,
IsEnabled BIT NOT NULL DEFAULT 1
);
CREATE TABLE dbo.RestoreVerifyLog
(
VerifyID BIGINT IDENTITY(1,1) PRIMARY KEY,
DatabaseName SYSNAME NOT NULL,
TestDate DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
RestoreSeconds INT NULL,
Succeeded BIT NOT NULL DEFAULT 0,
CheckDBPassed BIT NULL,
RTOTargetSeconds INT NULL,
RTOBreached AS (CASE WHEN RestoreSeconds > RTOTargetSeconds
THEN 1 ELSE 0 END) PERSISTED,
BackupsUsed INT NULL,
ErrorMessage NVARCHAR(2000) NULL
);
The computed column flags in the RTOBreached were automatically triggered whenever a restore took more time than expected. This means that you are aware of the delay in recovery time while every backup appears to be normal.
Step 2: Identify the connection from the source server
Since backup data is available with the production system, the discovery queries are run there via the linked server. The below is the full backup lookup which is utilizing parameterized dynamic SQL that targets [LinkedServer].msdb.
DECLARE @qLink SYSNAME = QUOTENAME(@linkedSrv);
SET @histSql = N'
SELECT TOP 1
@fPath = bmf.physical_device_name,
@fFinish = bs.backup_finish_date
FROM ' + @qLink + N'.msdb.dbo.backupset bs
JOIN ' + @qLink + N'.msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @db AND bs.type = ''D''
ORDER BY bs.backup_finish_date DESC;';
EXEC sp_executesql @histSql,
N'@db SYSNAME, @fPath NVARCHAR(260) OUTPUT, @fFinish DATETIME OUTPUT',
@db = @DatabaseName, @fPath = @fullPath OUTPUT, @fFinish = @fullFinish OUTPUT;
The same approach applies for the latest diff made after the full backup (type = 'I'), and a collection of log backups that follow the start of the chain (type = 'L', sort by finish time). The start of the chain corresponds to the finishing time of the last diff, if there was one; otherwise it is equal to the finishing time of the full backup.
It is worth noticing that in fact, this is a pretty simple way to select the logs, but it works in most cases. While the rigorously correct method of filtering based on LSN would mean using a log, which can be more than diff's finish time, the restoration process is likely to place the diff correctly after the log anyways, thus the optimum is not much worse than the strictly correct conclusion.
Step 3: Make the backup files reachable
However, here is where the trouble comes. SQL Server keeps the path of the backup job - and the instance that is rehabilitated attempts to access the same path as well. In case the production server backs up to a UNC share like \FILESRV\Backups\Orders.bak , the restoration server sees the file while reading it through the network and the restore will be fine.
However, if the production shuts down on a local folder such as E:\Backups\Orders.bak, that folder on the restoration box refers to its own E: drive - an alternate folder that is empty. Hence the restoration is not achieved, and the system throws an error - "File not found". So the file does not get transferred automatically.
The solution is to provide a mapping of the source folder - you need to inform the harness of how the local folder from the source server correlates to the folder that can be accessed through the restoration box via a shared user agreement. It is not smart enough to guess the name of the shared folder – you need to give it to the system yourself.
INSERT INTO dbo.RestoreVerifyPathMap (SourceServer, LocalPrefix, NetworkPrefix)
VALUES (N'PRODSQL01', N'E:\Backups\', N'\\PRODSQL01\Backups\');
A small function rewrites each discovered path through the map before it's used, with the longest matching prefix winning so you can layer a general rule and more specific ones:
SELECT TOP 1 @local = LocalPrefix, @net = NetworkPrefix
FROM dbo.RestoreVerifyPathMap
WHERE SourceServer = @SourceServer
AND LEFT(@RecordedPath, LEN(LocalPrefix)) = LocalPrefix
ORDER BY LEN(LocalPrefix) DESC;
RETURN @net + SUBSTRING(@RecordedPath, LEN(@local) + 1, 260);
If paths appear accessible even after mapping, the harness provides early notification instead of the usually unclear restore failure message. If your backups already reside at a UNC share, you can skip this table altogether, those paths are already accessible in their present form.
Step 4. Restore application of pertinent recovery flags followed by verification
Given that you are now armed with reachable paths, it is possible to construct the RESTORE procedure. The common misconception is that all steps other than the final one should employ the NORECOVERY flag meaning the database will be prepared to accept further backup applications. This is to be followed only by the final restoration step. Moreover, it is necessary to use the MOVE operation thereby directing the files to the scratch instance drives. The harness utilizes RESTORE FILELISTONLY from the whole backup to identify the logical names and create the MOVE operations accordingly.
RESTORE DATABASE [RVTEST_Orders_20260805] FROM DISK = '\\PRODSQL01\Backups\Orders_full.bak' WITH
MOVE 'Orders' TO 'D:\RestoreTest\RVTEST_Orders_..._Orders.mdf',
MOVE 'Orders_log' TO 'D:\RestoreTest\RVTEST_Orders_..._Orders_log.ldf',
REPLACE, NORECOVERY, STATS = 5;
RESTORE DATABASE [RVTEST_Orders_20260805] FROM DISK = '...diff.bak' WITH NORECOVERY;
RESTORE LOG [RVTEST_Orders_20260805] FROM DISK = '...log1.trn' WITH NORECOVERY;
RESTORE DATABASE [RVTEST_Orders_20260805] WITH RECOVERY;
Run it for real and the procedure wraps the restore in timing and error handling, then does the part that actually matters, proving the restored copy is sound, not just present:
BEGIN TRY
EXEC sp_executesql @restoreSql;
SET @secs = DATEDIFF(SECOND, @start, SYSUTCDATETIME());
SET @ok = 1;
BEGIN TRY
EXEC sp_executesql N'DBCC CHECKDB(...) WITH NO_INFOMSGS, ALL_ERRORMSGS;';
SET @checkOk = 1; -- CHECKDB raises on corruption, so clean = pass
END TRY
BEGIN CATCH SET @checkOk = 0; END CATCH;
END TRY
BEGIN CATCH SET @ok = 0; END CATCH;
Executing this CHECKDB step will make a difference between "backup was restored" and "backup was restored and got the correct data." Even if the backup was restored properly, it may still contain corrupted data transferred from the corrupted source. After that, the harness creates the report and drops the temp database in a TRY/CATCH so that the cleanup process cannot leave any abandoned files that might take up space.
Before anything else, use the dry-run option (@Execute=0), it prints out the exact plan without doing anything. This is the cheapest insurance you can get.
Step 5: Schedule and check the results
Register your databases, add the path map if you need it, and put the "verify all" driver to the nightly Agent job created for the restore instance. Only this will allow you to answer the questions that the backup job never could.
SELECT DatabaseName, TestDate, Succeeded, CheckDBPassed,
RestoreSeconds, RTOTargetSeconds, RTOBreached, ErrorMessage
FROM dbo.RestoreVerifyLog
WHERE TestDate >= DATEADD(DAY, -7, GETDATE())
AND (Succeeded = 0 OR CheckDBPassed = 0 OR RTOBreached = 1)
ORDER BY TestDate DESC;
Empty result: every backup that you are concerned with was restore properly last week. So see how it is done before it results in disaster!
Where could I take it from here
Well, having that action you have the point in time evidence. The next thing to do is to trend it, to track the restore duration getting closer and closer to the RTO values in the course of weeks or the restore test that has stopping without your notice. I have elaborated such framework that is gathering such information so it can easily score various DR readiness parameters over time. Both solutions are now presented on GitHub:
https://github.com/deepeshd87/sql-restore-verify
Nevertheless, you do not have to adhere to any of that to commence the process. Create an unpublished instance, establish a linked server for one source, register a single unimportant database, go through the dry run, and then you are good to go. Once it has taken the very first backup that did not get restored, which it will eventually do as the number of databases increases, you will think about how long you were oblivious to it all.
Having backup copies is far from being the same as being able to restore them.
Top comments (0)