Azure overwrite existing database copy

Robert Sheppard 1

I have a production database and a copy of the production database that I use for testing. So Im paying for 2 databases in my prescription. I would like to update the copy once in awhile with updated production data. Is there a way to do this? Do I have to delete the copy and re-create it? If so how does that affect my subscription? I would have to go through the copy process again... I dont want it do create a third database. Is there a better way to manage this scenario. Again I want a copy of my production database that I an use for testing and I want it to be updated with production data on a schedule or manually.

2 answers

Patrick C 81 Reputation points

2022-02-02T20:47:31.727+00:00

Hi,
It would be useful to have a bit more details. For instance, are you using it only to test? What are you using in order to develop?

You could use something like this from a TEST server:

DROP DATABASE IF EXISTS [YOURDBNAME]
CREATE DATABASE [YOURDBNAME] AS COPY OF [YOURPRODUCTIONSERVER].YOURDBNAME;

See https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy for more info

Depending on how your users/logins are configured ( contained users vs logins) you will need to fix them each time.

Hope it helps!
Patrick
Please sign in to rate this answer.
Robert Sheppard 1 Reputation point

2022-02-02T21:48:53.127+00:00

To clarify. I have one azure sql server and I would like to sync two databases on that same server. One database is production and one database is staging (testing). I created the staging database originally by using the "copy" feature... I think it would be better to have a one way sync from the production database to the staging database... alternatively I guess I could just delete the staging database everytime I want to update it and then use "copy" again. No idea how this affects my billing though. I hope that makes more sense.

Note: I do not have an on-site Sql database. Only Azure sql database.

Patrick C 81 Reputation points

2022-02-02T22:52:05.077+00:00

Depends on a few factors.

From my experience synching in order to test often has flaws since you need to almost predict what you bugs you will have.
For instance if you synch only data, and the schema changes you either need a different process for that or decide not to test it. You really need to make sure that (almost) everything that can cause an issues has been duplicates/synched over.

Really depends on your particular use case.

From I have found that the "drop create" deals with many of those cases. Of course if you need test data you would need to store it somewhere or script it.
I do not believe the billing would be affected much since you would still only have 2 databases at a time. (not a expert on billing) make sure you scale you "staging" DB to the correct level.

Both options provided (referring to this one and AlbertoMorillos) are valid depending on what you need and what type of scripting you want to do.

You could potentially explore renaming the "STAGING" to "STAGING_DATE" and scale it way down and keep it for a few days, like this if you have any test data you need you would be able to retrieve it.

Another option would be to use a 3rd party like Redgate SQL compare and use it to synch over the changes you decide in the project settings.

Hope it helps.
Sign in to comment
Alberto Morillo 32,891 Reputation points MVP

2022-02-02T21:06:52.44+00:00

Robert,

To my understanding you want to avoid the process of dropping the test database and recreate it again via a copy operation. You also want to manually update the test database with data coming from Production, or update it once a month.

Please consider using SQL Data Sync tp sync your Production database with the test database either manually or automatically (with a monthly frequency). You can get started with SQL Data Sync here. You can sync both Azure SQL databases.

Hope this helps.
Please sign in to rate this answer.
Robert Sheppard 1 Reputation point

2022-02-02T21:31:39.43+00:00

Close... I have one server and I would like to sync two databases on that same server. One database is production and one database is staging (testing). I created the staging database originally by using the "copy" feature... I think it would be better to have a one way sync from the production database to the staging database... alternatively I guess I could just delete the staging database everytime I want to update it and then use "copy" again. No idea how this affects my billing though. I hope that makes more sense.

Alberto Morillo 32,891 Reputation points MVP

2022-02-02T23:34:58.003+00:00

he SQL Data Sync service it self is free but there are charges for data movement, resource usage and egress charges. Since the Production database and the Staging database will be located on the same region there will be no egress charges.

Basically, in your scenario SQL data Sync and Drop/recreate the Staging database will have the same billing impact, you will be charge for the reserved resources hey have assigned on per hor basis.
Sign in to comment