1

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?

enter image description here

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).

0
4

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"

2
  • If I change the restore mode from Standby to Recovery, I cannot revert back to Standby mode, correct?
    – Java
    May 25 at 23:22
  • Correct, once you restore with Recovery, the database will come Online an no other backup will be restorable on top of it. May 26 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.