Restore standalone database to Always on availability replica

Lee Vaughan 1 Reputation point
2022-11-10T11:58:51.967+00:00

I am trying to restore a standalone database (not one of availability databases) using a .bak file to the Secondary replica in an Always On Availability group. The restore works fine up until about 50% progress when the whole Availability group freezes. If I stop the restore the group instantly un-freezes and can be accessed again. The backup is approximately 900GB. I have tried the same process with a much smaller database and it works fine. This is the SQL I am using to restore:

RESTORE DATABASE [NAME] FROM DISK = [LOCATION] WITH FILE = 1 MOVE [DATA FILE] TO [LOCATION] , MOVE [LOG FILE] TO [LOCATION], NOUNLOAD, REPLACE, STATS =5

Any suggestion as to why this might be happening?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,270 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. PandaPan-MSFT 1,906 Reputation points
    2022-11-11T08:55:52.52+00:00

    Hi @Lee Vaughan
    How about trying offline your secondary replica in your Always On Availability group and restore your 900gb backup. When you finish, you add the database back to your ag.


  2. Jon Gurgul 1 Reputation point Microsoft Employee
    2022-11-11T11:18:31.543+00:00

    I suggest that the most likely scenario is that at 50% restored you have run out of disk space. What errors are you getting?


  3. Bjoern Peters 8,856 Reputation points
    2022-11-11T13:06:39.47+00:00

    Hi @Lee Vaughan

    After I read through all messages above, let me ask some questions...

    That database you took a backup of is not a part of an AG? Correct?
    And you are not want to have it as a part of an AG? Correct?

    You just want to use the Secondary of that AG cluster as a normal SQL Server and test a restore?

    If all of the above => my understanding... then the only mistake I could think of is... how do you connect to the secondary?

    If you are using the Listener name to connect for the restore... SQL server "thinks" that you want to have the restore done in the AG... instead use the regular server name for the connection.

    Otherwise, as @Jon Gurgul suggested... please tell us about the error message you are receiving or what else you could see on your AG cluster that happens during your restore...
    What I could imagine is, for example, if your disk layout is not good, and backup, data files and tlogs are on one disk... everything must slow down because of the massive IO, and then - if your AG is configured as synchronous, the commit is too late (or is taking too much time) and everything has to wait for the IOs...

    In this case, it might be an idea to pause the data distribution (at least as a test) and try the restore again.