SQL Server restore stuck at 0%

If your SQL Server restore stuck at 0%, this could be related to the allocation of disk space for the data files.

Instant file initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used. This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in disaster recovery operations as the first phase of a restore is always to create the requisite files, if they don’t already exist. Cutting minutes or even hours from this phase can significantly reduce downtime and it’s in all editions of SQL Server from SQL Server 2005 onward. You can get more details from a blog post of Kimberly’s from March 2007 and note that it only applies to data files (see here for why not for log files).

The way to enable it is to give the SQL Server service account (or group that the service account is part of) the ‘Perform volume maintenance tasks’ privilege and then restart the service, or during installation/using the config tool for 2016 onwards.

[This above information has been lifted wholly from one of the reference articles]

To check if it is enabled (or disabled)

select * from sys.dm_server_services
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
CREATE TABLE #xp_cmdshell_output (Output VARCHAR (8000));
GO
INSERT INTO #xp_cmdshell_output EXEC ('xp_cmdshell "whoami /priv"');
GO
IF EXISTS (SELECT * FROM #xp_cmdshell_output WHERE Output LIKE '%SeManageVolumePrivilege%')
PRINT 'Instant Initialization enabled'
ELSE
PRINT 'Instant Initialization disabled';
GO
DROP TABLE #xp_cmdshell_output;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE;
GO

The query above will likely report the permissions being enabled or disabled for the privilege's that SQL Server is running as. If the setting is disabled and you are running SQL Server as a non-admin user (as you should be), you should go continue along to the resolution.

Resolution

Enable Perform volume maintenance tasks in local or security policy

• Create a GPO in Active Directory specific for SQL Servers Volume Maintenance, move the SQL Servers into the OU required

• Run gpupdate if you run Active Directory
• Restart SQL Server
• Run the check script above, you should see SQL Server correctly reporting it as being enabled

Reference articles

• How to tell if initialisation is enabled?
• Disk initialisation how/why?
• Perform maintenance tasks security policy?