SQL RESTORE WITH RECOVERY; 100% hanging

Question

I have done a lot of research on this.

I am trying to restore a database with SQL Server 2014 and it is holding at 100%.

Many people believe that the solution is to simply make sure that you have restored using the RECOVERY option.

I tried this and it still hangs 100%. I tried through the SSMS recovery dialog, and I tried to execute the following SQL statement:

USE [master] RESTORE DATABASE [MyDB] FROM DISK = N'C:\MyDB_backup_2015_05_05_010004_1506557.bak' WITH FILE = 1, MOVE N'MyDB_Data' TO N'F:\MSSQL\DATA\MyDB.mdf', MOVE N'MyDB_Log' TO N'F:\MSSQL\DATA\MyDB_1.ldf', NOUNLOAD, REPLACE, RECOVERY, STATS = 2 GO 

When I check the status of a command with:

 SELECT r.status, r.command, r.wait_type, r.percent_complete FROM sys.dm_exec_requests r WHERE r.command like '%restore%' or r.command like '%backup%' 

I get:

 status: suspended command: RESTORE DATABASE wait_type: BACKUPTHREAD percent_complete: 100 

From my reading it follows that RESTORE is waiting for BACKUP to complete, but there is no BACKUP command returned from my request in sys.dm_exec_requests

EDIT: after retrying the request and executing the above request, to see the RESTORE progress from the very beginning, I see that the value of “percent_complete” is steadily growing, despite the fact that the “status” remains “suspended” and “wait_type 'remains as' BACKUPTHREAD '.

Thus, although it has been “paused,” it still performs RESTORE.

So I'm at a loss ...

Anyone have any ideas what is going on here or what are some tips for diagnosing the problem?

Hooray!

Answer 1

As it turns out, the problem is related to the environment and quite straightforward:

First of all, I tried to create a backup from a backup of the original database.

The size of the log files was actually a known issue, so we usually back up from a shortened version of the database.

So, if someone has a similar problem, first try compressing the database first, then backing it up and restoring from it.

Secondly, I tried to archive the database to an external drive via USB3.

In addition, interestingly, I watched the progress of the working recovery team, and it also had the status “suspended” with “wait_type” “BACKUPTHREAD” - even while it was still ongoing (as evidenced by the percentage of completion completion in the percent_complete column) ! So I'm still confused as to what this is about ...

But at least I can restore my backups now :-)

Answer 2

I had the same problem as due to the size of the database. Although the backup shows 9 MB, when I right-click on the database in SQL Server Management and select properties, the size showed 25 GB! What I did was that I changed the database to "Easy recovery", again compressed the log file, performed a backup, and now I can restore it.