3

I know I can clone DB into a new one with

CREATE DATABASE Database1_copy AS COPY OF Database1;

(https://learn.microsoft.com/en-us/azure/sql-database/sql-database-copy-transact-sql)

and this goes flawesly, except in Azure, where db properties are managed by Azure portal, so I am try to find a way to copy most of the schema/resources/data into an EXISTING DB

would be great for:

CLONE DATABASE Database_test AS COPY OF Database_production

[even first approach has been to "clone" the entire db, indeed few tables on destination db should be kept, so better approach would be to CLONE EVERYTHING EXCEPT ('table1','table2'). Actually plan to achieving this by scripting the few tables needed on destination db and overwriting them after import, but bet solution would be the other]

pGrnd2
  • 514
  • 1
  • 5
  • 14

2 Answers2

-2

You can do this in several ways:

  1. Through the Azure Portal
    • Open your database in the Azure Portal(https://portal.azure.com)
    • In the overview blade of your database select the "copy" option enter image description here
    • Fill in the parameters, in which server would you like the copy enter image description here
  2. Using a sql server client and connecting to the server
    • Open your SQL Server blade in Azure
    • Select the "Firewall" option
    • Click on "Add client IP"
    • Connect to your database with your connection string and your favorite client, could be SSMS
    • Execute your sql query to clone the database in the same server
feranto
  • 497
  • 4
  • 14
  • how this would be done? "Execute your sql query to clone the database in the same server" ?? **I canpt find the query to clone the database** mine has over 1200 elements, no way to go one by one – pGrnd2 Jan 28 '17 at 00:13
  • 5
    These will create a new copy, not overwrite an existing one. – IronSean Oct 11 '19 at 12:49
-2
-- Copy a SQL database to the same server
-- Execute on the master database.
-- Start copying.
CREATE DATABASE Database1_copy AS COPY OF Database1;

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-copy-transact-sql

The above SQL statement works perfectly fine as expected in Azure SQL Database.

Important Notes:

  1. Log on to the master database (System Databases) using the server-level principal login or the login that created the database you want to copy.

  2. Logins that are not the server-level principal must be members of the dbmanager role in order to copy databases.

  3. Use updated version of the SQL Server Management Studio
juvchan
  • 6,113
  • 2
  • 22
  • 35