DB Env. Refresh

  • Tava

    Hall of Fame

    Points: 3816

    Hi, I've recently migrated from On Prem to PaaS and I has previously created an automated DB refresh from PROD to UAT (PROD to TST, PROD to DEV etc)

    However prior to refresh i would capture the UAT settings into a new table then remove the Prod data in the restored DB and then insert the captured data. All very simple when able to use cross server queries etc.

    I'm trying to create this process but am not sure as i rename the existing database to DBName_original and restore the prod DB as DBName. So aim would be data all from DBName_Original across.

    Is there any way? I think Data factory can be used and i think that's the only way but my Azure exposure isn't that great. Just hoping to avoid having a complex process.

    EDIT: accidentally created in Azure Development section, should really be in Azure Administration. MOD to move if possible.

  • Tava

    Hall of Fame

    Points: 3816

    forgot to add -  will be using PowerShell to automate all this. I can manually create a script and call it but it will need to be maintained which defeats the purpose every time to ensure its in sync.

  • Grant Fritchey

    SSC Guru

    Points: 395316

    If you're using Azure SQL Database I'd look into the COPY DATABASE command in Powershell. That's going to let you capture a quiet copy of the database and then you can use BACPAC to move it around to various servers. Because we're in the PaaS space, you can rename the database at will. It's not like on premises where you have to worry about moving files around.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Tava

    Hall of Fame

    Points: 3816

    Hi Grant, 

    Thanks for the reply, not sure If i misunderstood your answer or didn't explain properly.

    Below action Plan:

    1. Copy a production backup and restore as "Test_ProdBackup" onto  "UATServer1" using powershell- DONE
    2. Copy some configuration data from one Database ("Test") on "UATServer1" to "Test_ProdBackup" on "UATServer1" - IN PROGRESS

    If i took this away from PaaS i could do many way (e.g -  LinkedServer, cross-query etc) 

    So 2 options, i've been researching would be

    1. Data Factory
    2. Elastic Query (in preview - would probably do what a cross-query would do)

  • Tava

    Hall of Fame

    Points: 3816

    FYI, I've resolved the issue. I created it going down the path of using Elastic Query. it was a little bit clunky (in preview mode) but achieved the results.

    I followed steps from the below reference which was helpful. I had to tweak it for my needs as going from PROD to UAT elastic queries has some limitations.

    https://www.c-sharpcorner.com/article/cross-database-queries-in-azure-sql/

    If anyone is interested, I can put down my code for future reference

  • Grant Fritchey

    SSC Guru

    Points: 395316

    Maybe I don't understand what you're doing then. I don't know how you went from backups to Elastic Query as a way to solve a database refresh. Since the backups from Azure SQL Database are not available, the suggestion I made, creating a BACPAC on a copied database, is how you emulate backups. I'm sure I don't understand your needs adequately.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Tava

    Hall of Fame

    Points: 3816

    Yeah probably what I wrote and what I meant might have come across a little grey. I pretty much wanted a Prod Restore on the UAT Server, but keeping both existing UAT DB and just adding the newly restored DB. I was able to achieve that but my actual aim was to copy some configuration data from  the existing UAT DB and over write the newly restored DBs Data but just for a few tables, such as users, settings, links etc

    Since it was PaaS i couldn't work out how to transfer data from one DB to another automatically - this is where elastic came in and was able to automate the process.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply