0

I have two AzureSQL databases, let's call them "A" and "B". During our deployment process I want to copy schema + data from A to B. How to do that with as little as possible code? Is there a built-in task or PowerShell command to do that?

I've found https://learn.microsoft.com/en-us/azure/sql-database/sql-database-copy but this command creates a new database as the target. I don't want that, the target already exists.

D.R.
  • 20,268
  • 21
  • 102
  • 205
  • pretty sure your only option is import\export. well, copy option is the best one, obviously – 4c74356b41 Feb 18 '19 at 14:36
  • Seems there are quite a few ideas here. https://www.google.com/search?q=Clone+database+content+into+an+existing+database – Sean Lange Feb 18 '19 at 14:37
  • @4c74356b41 What kind of import/export do you have in mind? There are most certainly many options?! – D.R. Feb 18 '19 at 14:37
  • 1
    Do you want to replace the content of B with the content of A or do you want to add the content of A to B? – Alberto Morillo Feb 18 '19 at 14:38
  • @SeanLange: Have you had a look on the results? I couldn't find suitable information using Google. – D.R. Feb 18 '19 at 14:38
  • 1
    use MSSQL Management Studio, there is options to dump a DB into another one (schema + datas) – Cid Feb 18 '19 at 14:38
  • @AlbertoMorillo: Replace the content. – D.R. Feb 18 '19 at 14:38
  • @Cid: Have not found anything suitable for AzureSQL databases in SSMS. Also I want to automate it for our deployment process, so SSMS is no solution. – D.R. Feb 18 '19 at 14:39
  • Yes I did. If you simply refine the search parameters to include the DBMS you are using. – Sean Lange Feb 18 '19 at 14:40
  • @SeanLange: Would you please be so kind and post ONE link which is really describing my problem of copying a database into an existing one? Regardless of parameters I have not found a solution. – D.R. Feb 18 '19 at 14:41
  • azure sql got bacpac import\export feature, you can use that one – 4c74356b41 Feb 18 '19 at 14:41
  • @4c74356b41 I already looked at BACPAC. It supports exporting, but import only into new databases. Have I overlooked something? – D.R. Feb 18 '19 at 14:44
  • That is partly because this is not an easy task. You want the schema and the data. That means you have to build the schema and do all the inserts in a particular order to avoid foreign key issues. Or you could create the tables, then insert the data, then create all the constraints and such. There really isn't a shortcut here because it is pretty complex. – Sean Lange Feb 18 '19 at 14:46
  • There are however some pretty good third party apps that can do this kind of thing. But that is off topic here. It is however how I would go about the task you are facing. – Sean Lange Feb 18 '19 at 14:48
  • Would it not be simpler to just take a backup of the current DB create a BAK file and restore this to your SQL Server under a different DB name? – jimmy8ball Feb 18 '19 at 14:49
  • This would create a new database and I can't create new databases for two reasons: 1) it "destroys" all the monitoring our operations department has put on the existing database and 2) is a performance problem because creating a new database in Azure takes some minutes and our downtime should take seconds only. – D.R. Feb 18 '19 at 14:58
  • Sounds like you need to just roll up your sleeves and start hammering away. It isn't that tough, you just have to the inserts in the correct order to maintain RI. I would first create all the objects and skip the constraints, then use data export/import to copy all the data, and finally create the constraints. The only real time issues here for dev purposes is waiting for the data to copy. Everything else can be scripted out already. – Sean Lange Feb 18 '19 at 15:03

4 Answers4

0

Your best option is to drop the database B.

DROP DATABASE [yourdatabaseB];

Then create a copy of A as B.

CREATE DATABASE yourdatabaseB  
    AS COPY OF yourdatabaseA ( SERVICE_OBJECTIVE = 'P2' )

Although you don't want that is the quickest way to do it.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Unfortunately, I can't do this. This 1) destroys all the monitoring our operations department has on B and 2) is a performance problem because creating a new database in Azure takes some minutes and our downtime should take seconds only. – D.R. Feb 18 '19 at 14:57
  • Please run this queryselect * from sys.dm_db_resource_stats and you will see there is not performance impact. Test it with a database not in use. You will see 0% cpu, 0% data_io, 0% log_write. – Alberto Morillo Feb 18 '19 at 15:32
  • The copying database is an async operation. – Alberto Morillo Feb 18 '19 at 15:33
  • The B database will be more time unavailable with any other method due to high I/O usage. This may cause throttling on any non-Premium tier. – Alberto Morillo Feb 18 '19 at 15:35
  • Don't trust the portal for the duration on any operation. Always check "[sys].[databases].[status_desc]" column. What I have found that during copying process, it's value is always saying "COPYING" – Alberto Morillo Feb 18 '19 at 15:39
  • I'm not talking about I/O performance. I'm talking about how long the operation takes place. Copying a database takes about 2 minutes, using an existing one and just bulk copying the data is a matter of a sub second. – D.R. Feb 18 '19 at 16:24
0
  1. Script CREATE statements for every table
  2. Disable all foreign-keys
  3. Populate all the tables using SSIS wizard
  4. Re-enable foreign keys

DETAILS ... https://richardbriansmith.wordpress.com/2018/10/10/copy-tables-to-new-database/

RichardBSmith
  • 11
  • 1
  • 3
0

I would try to:

  1. Restore database A to C
  2. Drop database B
  3. Rename C to B
Jesús López
  • 8,338
  • 7
  • 40
  • 66
0

Can't you automate the monitoring setup? You can probably copy, wait for the copy to complete and then rename the databases. Renaming should be small enough downtime?

Vitor Tomaz
  • 81
  • 2