Automating restore of a database that is part of an availability group

• webrunner

  SSC-Dedicated

  Points: 31862

  More actions

  May 4, 2020 at 8:58 pm

  Go to Answer

  #3748790

  Hello experts,

  I've been tasked with automatically refreshing a dev database from a production backup each day. This task is fairly straightforward for a standalone server, but I am running into some complications when trying to set up the same for a database that has been added to an availability group. Also, in full disclosure, I have just started getting trained on AGs within the past year, so a lot of this is new to me.

  I've tried following the steps suggested at the link below.

  https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/

  However, it so happens that one node of the cluster I am working with doesn't seem to have the same common UNC path as the other two. Additionally, it seems that another way to do the AG restore is to:

  1. Remove the primary db from the AG.
  2. Restore the backup over the existing primary db.
  3. Drop the secondary dbs, disconnecting all users as needed.
  4. Add the primary db back to the AG.

  The link I referenced above does not drop the dbs; rather it restores from the common UNC path.

  So my questions are:

  1. What is the difference between the drop-secondary-db method and the restore-secondary-db method? And is one preferable to the other?
  2. Given that there are steps that need to be run on all the servers, is there a method or set of jobs that you can recommend that might be different from the ones mentioned in that referenced link?
  3. Is there some T-SQL or other way of finding a UNC path that is available to all servers?

  At the moment, the jobs I have set up are schematically as follows:

  Job 1:

  Primary Server - Create one job that does the following:

  Removes database from Availability Group

  Restores the database

  Sets recovery model of database to FULL

  Job 2:

  Secondary Server 1

  drop db

  Job 3: Secondary Server 2

  drop db

  Job 4

  On Primary Server

  Adds database to Availability Group

  Creates database backup and log backup

  I suspect DBAs out there are doing these automated restores in a much more elegant and efficient manner. Thanks for any help with this.

  -- webrunner

   

  • This topic was modified 4 years, 6 months ago by  webrunner.
  • This topic was modified 4 years, 6 months ago by  webrunner.
  • This topic was modified 4 years, 6 months ago by  webrunner.

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  May 5, 2020 at 4:04 pm

  Answer

  #3748995

  Are you saying your DEV system is setup with an availability group and you need to refresh the DEV database(s) from production on a daily basis?

  I see no reason to have a development environment setup and configured this way.

  As for the steps to be taken - you can actually restore the database to both the primary and the secondary from the same source backup and enable the database without having to backup the database from the primary.  The steps are:

  1. Remove database from AG
  2. Restore database on primary with recovery - disable tlog backups for this database
  3. Restore database on secondary with no recovery
  4. Join database to availability group
  5. Enable tlog backups on primary

  The key is that the restored database must be at the same LSN on both systems when the database is joined.  As soon as a new tlog backup is performed on the primary you cannot join and then must take another backup and log and restore to secondary.

  I would also recommend NOT dropping the databases on the secondary - is isn't needed.  In fact, there are several settings that can be made on a secondary database that will be maintained when restoring over the existing database that could be lost.  For example, if you have to change the owner of the database on the secondary - if you restore over the existing database the owner isn't updated - but if you drop the database the owner will now be the account the performs the restore.

  As for where you get the backup files...you can either restore over the network using a common UNC path or better yet - copy the backup file from the source to each destination and restore locally.  The local restore will execute and perform much faster than restoring across the network.

   

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs