SQL 2012 - restore doesn't complete

I am using a maintenance plan to back up a 2012 database.  I am doing a full backup with compression on.  When I try to restore the .bak to another 2012 server using SSMS Restore Database method it just stays 'executing'.  I even left it over night and it never completed.  If I use the attach method of the current mdb file it works perfectly.  What is going on?
KCTechNetAsked:
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.

pcelbaCommented:
Is it possible to restore non-compressed backup file?
Is it possible to restore a file created as "Copy only" backup?
What db recovery model do you use?
What is the db and log size?
0
KCTechNetAuthor Commented:
I need to restore a bak from last week which was compressed and not 'copy-only', so items 1 and 2 you provided are not an option.  we use 'full recovery' mode, and the bak file is about 22 gigs.

so why is the restore not finishing?
0
KCTechNetAuthor Commented:
are you thinking our months of backups are no good to use for restoring and we need to start doing 'copy-only' or non-compressed to be able to restore in the future?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Even if it is having issues it should report in few minutes and not run for the whole day. Have you checked whether there are any operations blocking the restore operation or not.
If not, then try using RESTORE HEADERONLY option to see whether your backup is in proper condition or not.
1
pcelbaCommented:
So lets suppose the backup file is correct and you have enough space to restore it.
The restore is waiting for blocked resource most probably. You may issue  

SELECT * FROM sys.dm_os_waiting_tasks

and check what's the db restore session waiting for. (The restore process has its own session_id which you may find in connection properties, to list all running sessions you may use sp_who2.)

You should ensure a few things for the smooth restore:
- you should restore to a different location (don't overwrite original db and log files)
- even when restoring to a new db you should ensure nobody uses the original db during the restore (you may e.g. rename it) This is not valid when you are restoring to a different SQL Server where no such db exists yet.
- you must have restore plan which affects restore parameters - this means proper RECOVERY option and possible transaction logs restore

More about RESTORE with (NO)RECOVERY: https://blog.sqlauthority.com/2009/07/15/sql-server-restore-sequence-and-understanding-norecovery-and-recovery/
0

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
KCTechNetAuthor Commented:
yes, I am restoring to a different server where this database doesn't exist.

It looks like changing it to No Recovery was the thing. Now I see a percentage in the status bar and within a minute it was at 10%.  With Recovery it would not show me any percentage, even after hours.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you performing the Restore? Best option is using the RESTORE command from a query window.
Are you using SSMS GUI? If so, don't forget to Refresh as it won't refresh automatically.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
While performing the RESTORE database operation, try running the below query to show the actual percent completed.
select percent_complete,* 
from sys.dm_exec_requests
where command like 'RESTORE%'

Open in new window


Also check for any blocking operation on the spid for the session_id captured above.
0
pcelbaCommented:
Hi KCTechNet,

it seems the answer ID: 42514542 recommending to start with NORECOVERY option helped to do the restore. If that is right please close the question if not then please ask for additional details.

TIA
Pavel
0
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
Databases

From novice to tech pro — start learning today.