SQL db restore keep spinning at 100% for hours

We have 200gb SQL (2008 R2) DB which I am trying to restore to a new empty db.
it takes about 1 hour to restore from 0% to 100% however its never finish the task and spinning an hour glass for over 6 hours.
there are no processes running on the SQL and i tried it for the second time . first time    gave up after 3 hours.
Data partition still has 200gb  of free space (after mdf file restored 200gb)
log partition still has 40gb space (log files are 70gb) after log file is restored
what is causing restore  ( i do a restore from device and point to a backup file on another local partition) not to finish?
leop1212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you restoring?
With T-SQL RESTORE command, with SSMS Restore option interface or a 3rd party software?

Can you post a screenshot?
leop1212Author Commented:
from the management console
sqlrestore.jpg
Vitor MontalvãoMSSQL Senior EngineerCommented:
Could be a bug from that screen. That's why I always prefer to run the T-SQL command (you can do it by clicking in the Script option instead of the OK button).
Check in SSMS in the Databases folder if that database is online and running. If so, then your restore screen hanged.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

leop1212Author Commented:
it says DB in 'Recovery'
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's not good.
It seems that the database has thousand of virtual log files (VLF). You can't do nothing but wait. After finish then first thing to do is to reduce the VLF's.
leop1212Author Commented:
ok. I will wait.
I only need store procedures from the backup.
is there a simple way to backup only store procedures (I have over 300 sp in my DB) instead of full DB backup
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. Script them.
For that right-click in the desired database and chose Tasks / Generate Scripts... Then follow the wizard.
leop1212Author Commented:
how to I reduce vlf? how long do you think it will take to finish restore?
is it just


ALTER DATABASE my_db
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file.
DBCC SHRINKFILE (my_db_log);
GO

-- Reset the database recovery model.
ALTER DATABASE my_db SET RECOVERY FULL;
GO
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunaly is impossible to know how much time will take. Restore finished by long for sure. The operation that's running now is Recovery (reading from VLF's and applying rollbacks).

The best way to reduce VLF's is to executing transactional log backups and shrinking the transaction log file until it reaches the minimum size (around 1MB) and then set the auto grow of the transaction log in chunks of 64MB (64, 128, 256, 512, ...).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leop1212Author Commented:
its took 3 days to finish backup
Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't forget to reduce the VLF's.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.