Restoring full back up taking forever

All we need is an easy explanation of the problem, so here it is.

I am new to DB Admin in SQL Server.

I am more of a SQL Developer.

I am trying to restore 95GB size of BAK file to SQL Server (in Azure VM).

The issue is that it is taking more than 24 hours now..

I realized that since I have multiple more BAK files to restore after this one, I have to restore the main BAK file (95GB one) with "NoRecovery".

So, I went into a folder where 95GB Bak file is located, and selected the Recovery state as "Restore with Norecovery".

Then, I just started the process since yesterday, and it is taking more than 24 hours.

What am I doing wrong? Or is there anything else I need to do?

Restoring full back up taking forever

As far as % complete is concerned, it was completed (with 100%) after probably 2 hours from when the process was started. But it just shows as "Restoring…" after 24 hours, and I am not able to do any query or access.

Regards to other bak files, those should be like an hourly transaction log backup. Since the original data provider only provides in SQB files format, I have to convert them into BAK file using a third party app (RedGate’s SQL Backup).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is the expected behavior.

When you restore using "With no recovery", it tell SQL to leave the DB in the "recovery" state (allowing you to restore subsequent backup).

If you want to make the database available, you can run a

Restore database [dbname] with RECOVERY;

Note that running this command will bring the DB online and you will be able to run query in it but it will also make all other restore impossible.

If you need to look at the data while in the middle of the restore process, you could use the "with standby" option

Restore database [dbname] from disk =N'path to backup' with STANDBY=N'path to the standby file that will be created'

Your other option would simply be to complete your restore (your transaction log backup) and make sure that on the last one, you change the "norecovery" by "recovery"

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply