asked on

SQL Restore Progress stays at 0 Percent when restoring from DataDomain Device

We have a nightly restore job that has been working flawlessly for years now. Recently, the restore has been hanging at 0 Percent Complete. (The database was taken offline prior to the restore. The status of the database never changes from offline to restoring).
Looking at the Activity Monitor, we are seeing that the restore is running with PREEMPTIVE_OS_FILEOPS wait types.

We are restoring from a DataDomain device. This is an intermittent problem. It works fine for 3 days, then it hangs up.
Rebooting the SQL Server clears up the issue (temporarily)

We are running SQL 2008 SP1 (Enterprise edition).
Our Data Domain device is a DD 2500 OS 5.7.2.0-532316

Has anyone experienced this?

Can anyone offer any tips as to what might be going on?

Thank you

Zberteoc

You can't restore an offline database. It has to be in online status. Change it back and then restart your restore.

https://blog.sqlauthority.com/2010/04/24/sql-server-t-sql-script-to-take-database-offline-take-database-online/

aiopsit

ASKER

Yes, you can (restore an offline database). We've been doing this for years on multiple systems without issue.

Zberteoc

Just take it online! Why would you take if offline in the first place?

aiopsit

ASKER

This is a test database server. We want to ensure there are no users accessing the database we are attempting to restore.

Zberteoc

Yes, I already gave you the link. Here is how you ensure that. Right before the restore you run this:

ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
RESTORE [myDB]...

Open in new window

Vitor Montalvão

This process is manual or are you using a script to run it automatically?
Btw, it's time to patch your SQL Server. If it's a known issue then the latest patches should fix it.

ASKER CERTIFIED SOLUTION

aiopsit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aiopsit

ASKER

We resolved our immediate issues by reverting back to the previous DataDomain device. There were no issues before pointing to our "new" DataDomain device, and there have been no issues since reverting back to the "old" device.