About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.
A common surprise in SQL Server recovery is the difference between backup time and restore time. A full backup that runs in an hour overnight is assumed to restore in about an hour. In practice, the restore can take much longer, and the extra time is spent in places a backup never goes.
Microsoft documents each of those places. This article separates the work involved; it is not an executable restore sequence, and turns them into questions for anyone who has promised a recovery time.
A backup copies data; a restore rebuilds files
In our understanding, a full backup reads the parts of the database that are in use and writes them out. Microsoft’s restore overview describes creating any missing database and log files, copying data, log and index pages from the backup, and applying the transaction log during recovery. File creation and recovery work help explain why elapsed restore time need not match backup time.
Microsoft's restore and recovery overview describes the last part: for most restores, it is necessary to apply a transaction log backup and let the database engine run its recovery process, which Microsoft describes as the process used by SQL Server for each database to start in a transactionally consistent - or clean - state.
Each of these steps can take longer than people expect.
Creating and zeroing the files
What happens by default
Microsoft's article Database instant file initialization explains that By default, data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. The files are filled with zeros first. Among the operations that trigger this, Microsoft lists Restore a database or filegroup.
In our reading, this is one possible reason a restore is slower than its backup. A database with a 2 TB data file that is half empty backs up roughly 1 TB of data. The restore must still create a 2 TB file, and without instant initialisation it must write zeros across the whole of it before or while the data is written back.
Instant file initialisation for data files
Instant file initialisation (IFI) skips that zeroing for data files. Microsoft explains that it reclaims used disk space without filling that space with zeros; old disk content is overwritten as new data is written.
It depends on a Windows privilege. Microsoft states that IFI for data files is only available if either the service account or the service SID of the Database Engine service is granted the SE_MANAGE_VOLUME_NAME privilege, which is granted through the Perform volume maintenance tasks security policy. From SQL Server 2016, setup offers to grant it at install time.
Two limits matter for recovery:
- Transparent data encryption. Microsoft notes that features such as TDE can prevent instant file initialisation for data files.
- Security. Microsoft's security considerations explain that, with IFI, deleted disk content is overwritten only as new data is written, so it is potentially accessible by an unauthorized principal until then. Microsoft nevertheless says: We recommend enabling instant file initialization as the benefits can outweigh the security risk.
In our reading, a recovery plan needs to know whether IFI is in effect on the server that will do the restore, not just on production. A rebuilt or standby server set up in a hurry may not have the privilege, and its restores will be slower than any test on a correctly configured server suggested.
The transaction log is different
The same article states: Historically, transaction log files couldn't be initialized instantaneously. From SQL Server 2022, Microsoft explains, transaction log autogrowth events of up to 64 MB can use instant initialisation, including on databases with TDE.
In our reading, that improvement covers small growth events, not the creation of a full-size log file during a restore. A database whose log file has grown to hundreds of gigabytes, for example after a large one-off operation, carries that size into every restore, and the log file is zeroed regardless of how little of it was in use. We recommend checking log file sizes as part of restore planning, and treating an oversized log as a recovery-time issue, not just a disk-space one.
Discovering the virtual log files
What VLFs are
Microsoft's transaction log architecture and management guide explains that the database engine divides each physical log file internally into several virtual log files (VLFs). Their number depends on how the log grew: the guide describes rules under which each growth event adds one, four, eight or sixteen VLFs, depending on the size of the growth and the version.
Why many VLFs slow a restore
The guide describes the consequence: If the log files grow to a large size in many small increments, they end up with many virtual log files. That can slow down database startup, log backup and restore operations, and affect replication and availability group redo.
It explains why: during the initial stages of recovery, SQL Server discovers all VLFs and builds a list of them, and The more VLFs, the longer the process. Among the symptoms it lists are Restoring a database takes a very long time to complete. and memory errors during restore.
Microsoft's recommendation is to create log files close to their final required size, with relatively large growth increments. In our reading, VLF count is a property of the database, so it travels with every backup and affects every restore of it until the log is managed.
Database recovery
The three phases
The restore and recovery overview describes recovery in three phases:
- Analysis, which reads the log to find the last checkpoint and build tables of dirty pages and active transactions.
- Redo, which rolls forward every modification recorded in the log that might not have been written to the data files.
- Undo, which rolls back incomplete transactions. Microsoft notes that after rollback, the database goes online and no more log backups can be applied.
The overview also describes Fast Recovery in Enterprise edition, which can bring a database online after redo while undo continues, in situations such as a failover or restart. It explicitly excludes a transition online when the SQL Server service has not restarted, giving SET ONLINE as an example that waits for all three phases. In our reading, this restart/failover behaviour is not a promise that bringing a restored database online skips its undo wait.
Long transactions and accelerated database recovery
A large transaction that was open at the time of the backup has to be rolled back during undo. Microsoft's article on accelerated database recovery (ADR) explains the traditional problem: recovery after a crash can take a long time if a long-running transaction was active.
ADR, introduced in SQL Server 2019 and improved in 2022 and 2025, redesigns recovery. Microsoft states that with it, Long-running transactions don't affect the overall recovery time. In our reading, ADR is relevant to restore planning for databases that run long transactions, such as large batch jobs, but whether it suits a given workload is a separate decision with its own trade-offs, which this article does not cover.
Applying the required backup chain
For a point-in-time recovery, the restore does not stop at the full backup. Differential and log backups are applied in sequence; the transaction log guide notes that they advance the restored database to a later time.
In our reading, this is where backup time and restore time diverge most. Log backups are taken in small pieces, every few minutes, while the system runs. A restore must replay all of them, one after another, in one sitting. A day of log backups that each took seconds to write may take a long time to apply, because every change they contain is redone. A recent differential backup shortens the chain, which is one of the main reasons to take them.
Where the restore happens matters
Everything above assumes the restore runs on a server like the original. In a real recovery, that is often not the case. The original server may be the thing that failed, and the restore may run on a standby, a rebuilt machine or capacity in another site.
In our reading, each step described here depends on the target, not the source:
- File creation and zeroing run at the speed of the target's storage. A test server on fast disk can make zeroing look cheap; a recovery site on slower or shared storage may not.
- Instant file initialisation depends on the target's service account and its privilege, as covered above.
- Reading the backup depends on where the backup files are and how fast the target can reach them. A backup stored in another site or in cloud storage adds network time before any of the database work begins.
- Recovery and log replay depend on the target's processors, memory and log storage.
We recommend measuring restore time on the target a real recovery would use, or on something deliberately no faster, and repeating the measurement when that target changes. A figure taken on the production server itself, during a quiet weekend, is likely to be the most optimistic number available.
Compression helps the copy, not every step
Microsoft's article on backup compression explains that a compressed backup typically requires less device I/O and usually increases backup speed significantly, at the cost of extra CPU. It notes that SQL Server 2025 adds a new compression algorithm, ZSTD. It also warns that column-level or application-encrypted data can compress much less; TDE has separate guidance. Measure the actual backup rather than assuming the same compression saving for every encrypted database.
In our reading, compression reduces the time spent reading the backup during a restore, especially over a network, though the restoring server then spends processor time decompressing it. It does not reduce file zeroing, VLF discovery, recovery or log replay, which depend on the database, not on the backup file.
Planning recovery times
From the points above, these are our recommendations:
- Measure restore time, not backup time, for each important database, on the kind of server and storage a real recovery would use.
- Confirm instant file initialisation on every server that might restore, and note where TDE prevents it.
- Know the file sizes, especially the log file, and have the database owner review whether its size and growth settings match the workload.
- Check VLF counts and manage log growth settings as Microsoft recommends.
- Keep log chains short enough to replay in the time allowed, using differential backups where they help.
- Allow for undo when long transactions run, and consider whether ADR is appropriate.
- Repeat the measurement after major growth, version upgrades or storage changes.
Keep the database recoverable until the chain is complete
The phases above explain sources of work, not a command sequence. A point-in-time restore must leave the database able to accept the remaining differential and log backups until the intended restore point is reached. Completing recovery too early prevents applying further logs to that restore sequence. The database owner should prepare the supported restore plan from the actual backup headers and recovery objective before execution.
For timing, record the full file sizes as well as compressed backup size, the backup location, target storage and whether the target has the expected file-initialisation privilege. Measure retrieval, file preparation, backup application and final checks separately where the tools expose them. A delay before percentage progress changes is not itself proof of a hung restore. Identify the work and resource constraint before considering changes, and do not turn an incident into an unplanned log shrink or recovery-setting experiment.
Risks and limits of this article
- Based on Microsoft documentation as read on 11 November 2026. Behaviour differs between versions and editions.
- The comparisons between backup and restore work, and the recommendations, are our reading and understanding, labelled as such. No restores were timed for this article.
- This article does not cover ADR's trade-offs, storage performance or network design.
- No commands or settings were changed or run, and Microsoft's scripts are deliberately not reproduced.
Sources
Writer research: 11 November 2026. Independently reopened for review: 13 November 2026.
- Microsoft Learn: Restore and recovery overview (SQL Server)
- Microsoft Learn: Database instant file initialization
- Microsoft Learn: SQL Server transaction log architecture and management guide
- Microsoft Learn: Accelerated database recovery
- Microsoft Learn: Backup compression (SQL Server)
This article was originally published on .