SQL RESTORE WITH RECOVERY; Hangs at 100%

Question

I have done a lot of research about this.

I am attempting to recover a database with SQL Server 2014 and it keeps hanging at 100%.

A lot of people suggest that the solution is to just make sure that you restore with the RECOVERY option.

I have tried that and it still hangs at 100%. I have tried via the SSMS Restore dialog and I have tried running 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 the command via:

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

Which from my reading implies that the RESTORE is waiting for a BACKUP to complete, but there is no BACKUP command returned from my query to sys.dm_exec_requests

EDIT: After trying it again and running the above query to watch the progress of the RESTORE from the beginning, I can see that the 'percent_complete' value is increasing steadily, despite the fact that the 'status' remains as 'suspended' and the 'wait_type' remains as 'BACKUPTHREAD'.

So despite it being 'suspended' it is actually still performing the RESTORE.

So I'm at a loss...

Anyone got any ideas what's going on here or any tips on how to diagnose the issue?

Cheers!

Answer 1

As it turns out the issue was environmentally related and pretty straight forward:

First of all, I was attempting to back up from a non-shrunk back up version of the origin database.

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

So if anyone is having a similar problem, trying shrinking the database first and then backing it up and restoring from that.

Secondly, I was attempting to backup the database to an external drive over USB3.

Also, interestingly, I watched the progress of the working restoration command and it too had a status of 'suspended' with 'wait_type' "BACKUPTHREAD" - even whilst it was still progressing (as evident by the percentage of completion increasing in the percent_complete) column! So I'm still lost as to what thats about...

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