Share via

Restoring a Backup Database to an Availability Group SQL server

  • Article

Restoring a Backup Database to an Availability Group SQL server

You will eventually need to restore your SQL AlwaysOn Availability Groups from backup. Hopefully not on live production servers, but on test environment as a way of verifying that your last resort DR (Disaster Recovery) solution works. Another scenario is when you need to restore from backup is in case of a Disaster, and you need to rebuild your servers from scratch. A third case would be, if you have a test environment that is used by the development team to test new application features, and you need to constantly update the test database from live data.

Things to remember. You need to remove the database from the AG. Restore the database, and re add the database to the AG with the Join only option.

  • Have the backup file of the database copied and ready on all servers that are members of the Availability Group
  • Open SSMS on the primary SQL server. Expand AlwaysOn High Availability à Availability Groups à Availability Group Name à Availability Group Databases
  • Right click on the data base and choose Remove Database From Availability Group…. On the window that opens click ok to remove the database.
  • Restore the database from backup to the primary. Select the restore option Restore With FullRecovery
  • Restore the database from backup to all secondary servers. Select the restore option Restore With NORecovery
  • Next we need to Add the database to the AG databases
  • Expand AlwaysOn High Availability à Availability Groups à Availability Group Name à Availability Group Databases
  • Right click on the data base and choose Add Database… On the window that opens click ok to acknowledge the Introduction page

  • Next you need to select the database that you want to add to AG Databases

  • Select Join only. click next

  • On the Connect to Replicas page you will be presented with Server instances of the secondary replicas. Click on connect to connect to each replica.

  • Click next through the validation page. And next to accept Summary page.
  • After you add the database to the AG databases, it will appear on the secondary SQL servers under AG Databases. You might need to refresh the database on the secondary SQL servers to see their status change from Restoring to Synchronized.