File Initialization During SQL Database Restore Takes Longer Than Expected

The file initialization of a SQL database that is 1 TB or larger takes a long time, even before the data restore step begins.

Symptoms

A SQL server database restore takes more time than expected.

Causes

If instant file initialization (IFI) is not enabled for the restore of a SQL database, the data and log files are initialized to zeros to overwrite any data that remains on the disk from previously deleted files. This initialization adds to the total time that it takes for a restore to complete.

Resolution

For SQL databases bigger than 1 TB, enable IFI for the restore of the database. This allows the restore operation to skip the zeroing of the space allocated to the database and to begin using the space immediately for data files. The content of the allocated space is overwritten as new data is written to the files.

For information about enabling IFI, refer to the SQL Server documentation.