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?
 
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
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
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 & ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.