December 18, 2018 at 8:40 pm
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.
December 18, 2018 at 8:42 pm
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.
December 19, 2018 at 4:21 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2018 at 4:12 pm
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)
December 19, 2018 at 10:45 pm
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
December 20, 2018 at 4:49 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2018 at 3:14 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply