SQL Server faster restores with instant file initialisation

Instant File Initialization can make SQL Server Restores much faster  .

A typical SQL Server Restore phase includes the following phases:

a) Copy data,logs,index from backup to the database files

b) Take logged transactions and apply to data for recovery point rollforward.

Next step  to undo phase – to deal with uncommitted transactions.

c) Undo takes uncommitted transactions and rolls back (which depending on the transaction can take awhile to rollback)– then making the database available for use

 But , previous to these steps and in the process of restoring – the initialisation process of the data\log files fills the previously deleted files with zeroes .This is a OS security feature and is designed to avoid revealing information from previously deleted files .

In terms of SQL Server  for certain situations (create database, restore database – when in conjunction with CREATE DATABASE ,increase size of a file, add files to a db)  the OS will attempt to zero out .

The file initialisation process delays the restore process - because SQL Server initialises files by filling the space with zeroes. Just to  clarify – this is in situations where a CREATE DATABASE is required as part of the RESTORE process

Is there a quicker way of restoring?

 In certain situations ,yes, by applying Instant  file initialisation .skips the the zeroing out of the data on disk – i,e it does not overwrite the existing data . In effect the OS allocates the space.

How to set up instant file initialization?

Prereqs: sql server 2005 , either Windows XP , widnows 2003 or later

1) Run the MSSQLSERVER service account with a Windows Acct

2)Assign SE_MANAGE_VOLUME_NAME special privilege to that Windows Account

Do this by :

On cmd line : gpedit.msc

Then : Windows Settings | Security settings | Local Policies | User Rights Assignments

Open “Perform volume maintenance tasks”   and and add the SQL Server service account

3) Recycle SQL Server instance

To check the instant file initialization status read Get Instant File Initialization status from sys.dm_server_services

 Note :

1) There are security consideration to analyse. The risks are minimal – but exist

2) The benefits derived from instant file initialization – only relate to the data files not the log files. You can test this out by restoring different databases with different size data and log files

3) In production environments  I use this in some situations – not all restores.

Read Also about estimating database restore time and restore history

SQL Server – Restore Database Estimated Finish Time

SQL Server - Display restore history for a single database