Forum Discussion

Denise Child's avatar
Denise Child
Iron Contributor
Jul 15, 2022

Refresh Azure SQL Server from Prod to Test across different subscriptions and regions

We are looking for guidance on using an Azure Automation Account with Powershell and a Managed Identity to refresh Azure SQL Server from Prod to Test across different subscriptions and regions.

Here are the details:

recurrence weekly
drop test db
drop dev db
create test db from copy of prod db
truncate test db (4 tables)
shrink test db database
update service level test db
add permissions test db
create dev db as copy of test db
update service level dev db
send an email when completed

 

I have created the Azure Automation Account. Not sure what the next steps are.

This is my first time so any help will be much appreciated.

I went through the Runbook Gallery to look at examples, and see the SQL Modules need to be loaded, but not sure of the order to create the runbooks or managed identity.

 

Thanks,

Denise

 

 

2 Replies

  • RobMSewell's avatar
    RobMSewell
    MVP
    Aug 30, 2022

    Denise Child 

    PowerShell is an awesome tool to do this with. Azure Function or Azure Automation are suitable code runners for the code (you could use many other things)
    You are going to have to write and test your own PowerShell to get this done (or hire someone with knowledge to do it)


    Remove-AzSqlDatabase will remove your test and dev databases

    New-AzSqlDatabaseCopy will create you a copy of a database within the same subscription but across subscriptions you would need to use SQL authentication and T-SQL

    You can then run T-SQL to truncate the tables, update the SLO and add permissions - This is best done with dbatools Invoke-DbaQuery

    You could send the email using the code runner (Azure Automation/Functions/GitHub Actions/AzureDevops etc)

    I hope that helps,

    Rob


  • Joey D'Antoni's avatar
    Joey D'Antoni
    Copper Contributor
    Aug 30, 2022
    your logic is sound--however, this will be a heavy lift for someone without extensive PowerShell experience. There's probably not a lot out there in the gallery. I'd work with the Learn modules for automation here. https://docs.microsoft.com/en-us/learn/modules/explore-azure-automation-devops/ to get more comfortable with automation and PowerShell.

Resources