When you perform a database restore operation using Management Studio Object Explorer, you might be baffled as to why the confirmation pop-up window doesn’t pop-up saying that the database restore operation is complete.

This is because the RESTORE operation in SQL Server can be divided into two stages if you are having a high level discussion and don’t want to get into the intricacies:

1. Copy the information from the backup media and write them into the physical files on the Windows File System

2. Perform recovery on the database to bring it online (provided in the OPTIONS tab you have selected the first radio button under the Recovery State)

The highlighted counter shows you the progress of the first stage only. The second stage doesn’t have a progress counter associated with it. If you look at the percent_complete column value in the sys.dm_exec_requests DMV.

So, if your database restore screen is showing 100% completed status and the SQL Server Errorlog is not reporting the following for the database being restored:

2010-03-13 02:56:22.650 spid61       Starting up database ‘distribution_new’.
2010-03-13 02:56:23.140 Backup       Database was restored: Database: distribution_new, creation date(time): 2009/08/04(01:37:56), first LSN: 865:804:70, last LSN: 865:833:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\distribution.bak’}). Informational message. No user action required.

Then you are phase 2 of the restore provided that session performing the database restore is not experiencing blocking.

Now for the most interesting part, Why is this happening i.e. the long recovery? One of the reasons is documented here.