asked on

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?

Pavel Celba

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?

KCTechNet

ASKER

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?

KCTechNet

ASKER

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?

Raja Jegan R

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.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

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

Start Free Trial

KCTechNet

ASKER

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.

Vitor Montalvão

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.

Raja Jegan R

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.

Pavel Celba

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