Sql db restoration 0 %

1

Hi,

Initiated 2.4 TB db restoration freshly in the sql instance using GUI.. From 2 hours it is showing as 0 %.

In SQL_Data drive occupied with 2.4 tb. But when i open the drive inside everything empty.

I feel the data allocation is taking time. But I am confused whether restoration is in progress or stuck in between?

Please suggest how to fasten the db restoration.

For info total 8 data files in the db. Two Split backups each 450 gb of each backup file.

please help.

Thanks,

Pols.

2

Do you have instant file initialization (IFI) turned on for the SQL Server you're restoring to?

If that's not set up, then the data files will be zero-written before the restore can begin. IFI does not affect the transaction log files. If the log file is more than 100 GB it will take a while as the entire size has to be zero-written before the restore can finish.

If IFI is turned off, then you should probably cancel the restore, turn it on, then try restoring again.

Other things that can slow down a database restore:

  1. Lots of virtual log files (VLFs) in the transaction log. If there are more than 1000 VLFs, they will incur additional activity once the data is restored (100% restore complete, but sits there for an extended time)

  2. Lots of BEGIN TRAN...COMMIT TRAN activity in the log during the backup process. Lots of small transactions in the log need to be redone during the restore, after the data has been restored. Similar to lots of VLFs, you'll see 100% restore completed but it will stay there for a while. This will be noticeable if you have >25K-50K transactions in the log.

You can improve restore throughput by increasing MAXTRANSFERSIZE and BUFFERCOUNT settings, documented here:

They won't help with the IFI, VLF or transaction redo, but the data restoration can be significantly faster.

You can also speed up restores by using backup compression and multiple backup files, although it might not help you now, it's good for future reference. Any database more than 1 TB in size should be backed up to multiple files, and I can't think of any reason NOT to use backup compression.

More info on VLFs here: