Restore Sequences, NORECOVERY/STANDBY, STOPAT, Piecemeal Restore, and Online Scenarios

Learning outcomes

A backup set becomes useful only when you can assemble the correct restore sequence on a safe target. This lesson turns “restore the latest backup” into a deterministic algorithm: choose the target recovery point, select the correct full/differential base, apply every required log backup in order, keep the database non-operational while more log must be applied, and recover only when the intended endpoint is reached.

1. NORECOVERY means “the sequence is not finished”

During restore, SQL Server copies backup pages, redoes logged changes, and eventually undoes incomplete transactions. WITH NORECOVERY deliberately postpones the final undo phase so additional differential or log backups can still be applied. Once you recover the database, you cannot continue applying older log backups to that recovery fork as though the sequence were still open.

2. Build a source timeline that includes a deliberate mistake

The next script creates a source database whose logical file names are predictable, takes a full backup, records a safe update, captures a restore target timestamp, then performs a destructive delete and backs up the log. The target timestamp is persisted in master.dbo.ServiceHubRestoreMarker so the subsequent restore script can retrieve it even after batches change context.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;USE master;IF DB_ID(N'ServiceHubRestoreTarget') IS NOT NULLBEGIN ALTER DATABASE ServiceHubRestoreTarget SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubRestoreTarget; END;IF DB_ID(N'ServiceHubRestoreSource') IS NOT NULLBEGIN ALTER DATABASE ServiceHubRestoreSource SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ServiceHubRestoreSource; END;DROP TABLE IF EXISTS master.dbo.ServiceHubRestoreMarker;CREATE TABLE master.dbo.ServiceHubRestoreMarker(target_time datetime2(3) NOT NULL);CREATE DATABASE ServiceHubRestoreSource;ALTER DATABASE ServiceHubRestoreSource SET RECOVERY FULL;GOCREATE TABLE ServiceHubRestoreSource.dbo.WorkOrder(work_order_id int PRIMARY KEY,status varchar(20),amount decimal(12,2),changed_at datetime2(3));INSERT ServiceHubRestoreSource.dbo.WorkOrder VALUES(1,'OPEN',100,SYSUTCDATETIME()),(2,'ASSIGNED',250,SYSUTCDATETIME()),(3,'OPEN',400,SYSUTCDATETIME());GODECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_full.bak';DECLARE @log1 nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_log1.trn';DECLARE @log2 nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_log2.trn';BACKUP DATABASE ServiceHubRestoreSource TO DISK=@full WITH INIT,CHECKSUM;UPDATE ServiceHubRestoreSource.dbo.WorkOrderSET status='CLOSED',changed_at=SYSUTCDATETIME() WHERE work_order_id=1;BACKUP LOG ServiceHubRestoreSource TO DISK=@log1 WITH INIT,CHECKSUM;WAITFOR DELAY '00:00:02';INSERT master.dbo.ServiceHubRestoreMarker(target_time) VALUES(SYSUTCDATETIME());WAITFOR DELAY '00:00:02';DELETE ServiceHubRestoreSource.dbo.WorkOrder WHERE work_order_id=2; -- incidentBACKUP LOG ServiceHubRestoreSource TO DISK=@log2 WITH INIT,CHECKSUM;SELECT * FROM master.dbo.ServiceHubRestoreMarker;SELECT * FROM ServiceHubRestoreSource.dbo.WorkOrder ORDER BY work_order_id;

The source now lacks work order 2. The restore target will intentionally stop after work order 1 was closed but before work order 2 was deleted.

3. Restore to a new database with MOVE and STOPAT

Never rehearse recovery by overwriting the only known-good source. Restore to a separate name and separate physical files. MOVE maps the logical files in the backup to new physical paths; it is especially important when restoring to another instance whose default paths differ.

DECLARE @root nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultBackupPath'));IF @root IS NULL THROW 51000,'InstanceDefaultBackupPath is unavailable; choose a writable backup directory explicitly.',1;DECLARE @sep nchar(1)=CASE WHEN CHARINDEX(N'/',@root)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@root,1) IN (N'/',N'\') SET @sep=N'';SELECT @root AS backup_root;DECLARE @dataRoot nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultDataPath'));DECLARE @logRoot nvarchar(4000)=CONVERT(nvarchar(4000),SERVERPROPERTY('InstanceDefaultLogPath'));DECLARE @dataSep nchar(1)=CASE WHEN CHARINDEX(N'/',@dataRoot)>0 THEN N'/' ELSE N'\' END;DECLARE @logSep nchar(1)=CASE WHEN CHARINDEX(N'/',@logRoot)>0 THEN N'/' ELSE N'\' END;IF RIGHT(@dataRoot,1) IN (N'/',N'\') SET @dataSep=N'';IF RIGHT(@logRoot,1) IN (N'/',N'\') SET @logSep=N'';DECLARE @full nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_full.bak';DECLARE @log1 nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_log1.trn';DECLARE @log2 nvarchar(4000)=@root+@sep+N'ServiceHubRestoreSource_log2.trn';DECLARE @target datetime=CONVERT(datetime,(SELECT TOP(1) target_time FROM master.dbo.ServiceHubRestoreMarker));DECLARE @mdf nvarchar(4000)=@dataRoot+@dataSep+N'ServiceHubRestoreTarget.mdf';DECLARE @ldf nvarchar(4000)=@logRoot+@logSep+N'ServiceHubRestoreTarget_log.ldf';RESTORE DATABASE ServiceHubRestoreTarget FROM DISK=@fullWITH MOVE N'ServiceHubRestoreSource' TO @mdf,     MOVE N'ServiceHubRestoreSource_log' TO @ldf,     NORECOVERY;RESTORE LOG ServiceHubRestoreTarget FROM DISK=@log1WITH NORECOVERY,STOPAT=@target;RESTORE LOG ServiceHubRestoreTarget FROM DISK=@log2WITH RECOVERY,STOPAT=@target;SELECT * FROM ServiceHubRestoreTarget.dbo.WorkOrder ORDER BY work_order_id;SELECT state_desc FROM sys.databases WHERE name=N'ServiceHubRestoreTarget';

Expected result: work order 1 is CLOSED, work order 2 still exists, and the target database is ONLINE. That proves the restore reached the business state represented by the target timestamp. It does not yet prove integrity, application compatibility, security mappings, Agent jobs or external dependencies—those validations belong to the recovery drill.

4. STANDBY is read-only between log restores

STANDBY='undo-file' performs enough undo to expose the database read-only and writes undo information to a standby file so later log restores can reverse that undo and continue. That makes it useful in log-shipping/reporting patterns, but active readers must be disconnected when the next log is applied and the standby file itself is an operational dependency.

-- RESTORE DATABASE ReportingStandby FROM DISK=@full--   WITH MOVE ..., NORECOVERY;-- RESTORE LOG ReportingStandby FROM DISK=@log1--   WITH STANDBY=N'<writable path>/ReportingStandby_undo.bak';-- SELECT ... FROM ReportingStandby.dbo.WorkOrder; -- read-only window-- RESTORE LOG ReportingStandby FROM DISK=@log2--   WITH STANDBY=N'<same undo file>';

5. Piecemeal/online restore is powerful but edition- and topology-sensitive

File and piecemeal restore let very large databases bring critical filegroups online before every archive filegroup has finished restoring. Microsoft’s current feature matrix keeps online page/file restore and several online restore scenarios in Enterprise-class capabilities. The free course path therefore teaches the restore sequence and filegroup design concept without requiring enterprise infrastructure.

Under FULL recovery, an offline file restore normally starts with a tail-log backup; online file restore has a different required log-backup sequence. Under SIMPLE, file restore is restricted to read-only secondary files. These are not details to discover during an outage.

SELECT name,state_desc,recovery_model_desc,user_access_descFROM sys.databasesWHERE name IN (N'ServiceHubRestoreSource',N'ServiceHubRestoreTarget');GOSELECT DB_NAME(database_id) AS database_name,file_id,name,type_desc,       physical_name,state_descFROM sys.master_filesWHERE DB_NAME(database_id) IN (N'ServiceHubRestoreSource',N'ServiceHubRestoreTarget')ORDER BY database_name,file_id;GO-- RESTORE FILELISTONLY-- FROM DISK = N'<replace with the full-backup path printed by the lab>'; -- template

6. Production judgment

Production judgment. A restore runbook should contain the exact media order, logical-file mapping, target paths, tail-log decision, STOPAT/mark policy, encryption-key prerequisites, access-control change, expected restore duration and validation steps. Test on a disposable target with production-like storage and representative backup size. “The syntax worked on a 20 MB lab” is not an RTO guarantee for a multi-terabyte production database.

Authoritative references

• Restore sequences under full recovery — restore sequence planning
• Point-in-time restore — STOPAT semantics
• RESTORE — NORECOVERY, RECOVERY, STANDBY and MOVE syntax
• Restore files and filegroups — file/filegroup recovery
• SQL Server 2025 editions and features — online restore and edition boundaries