SQL Server – Restore PROD Database to QA with AlwaysOn

availability-groupsrestoresql-server-2016

I want to create a script/job that will run weekly to backup my PROD database, and restore it to my QA server, then run a custom maintenance script against it to cleanup and prepare the data to be analysed.

The problem is that my QA server is running AlwaysOn Availability Group.
I have access to Powershell, "DBATools.io" scripts, SQL Server Agent, and a remote SQL server if it can help. All under SQL2016.

I also just discovered today the SQL2016 feature called AUTOMATIC SEEDING. I tried it, and it allows me to bypass the complicated "Add database to AG" steps.

So, up to now, I can simplify the steps down to this:

ALTER AVAILABILITY GROUP [SQLQA] REMOVE DATABASE [ProdData];

RESTORE DATABASE [ProdData]
        FROM DISK=N'\\nas\Backup\ProdData.bak'
        WITH FILE=1, NOUNLOAD, REPLACE, STATS=5, 
            MOVE N'ProdData' TO N'S:\Data\ProdData.mdf', 
            MOVE N'ProdData_log' TO N'T:\Log\ProdData_Log.LDF';

-- Missing a step here to physically remove the old database from the secondary server, at this point in "Recovery pending". If the old database is still there, the AUTOMATIC SEEDING will not work, nor the manual steps of adding a database to AlwaysOn for that matter.

ALTER AVAILABILITY GROUP [SQLQA] ADD DATABASE [ProdData];

-- Run custom maintenance/cleanup SQL code here

The big question is: what are my options to get rid of the old secondary target database?

Alternatively, anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Thanks in advance.

Best Answer

what are my options to get rid of the old secondary target database?

You can create linked server and use Execute At LinkedServer. This is what I am doing in my current scripts when I have to refresh AG env in Dev / QA from PROD.

You can use powershell, etc .. but Execute At LinkedServer just works fine for my needs.

anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Refresh scripts to downstream environments are specific to company environment esp. the post restore process like desensitizing or masking data, etc. So, I would say, invest some engineering time and make the process repeatable that will save you a lot of time in future.

Related Question