Share via

How can I script out copy DB manually? How can I know what should copy?

OGINTZ Marina 105 Reputation points
2024-02-28T08:39:57.7266667+00:00

Hi,

I need to copy DB from SQL server with private endpoint to another SQL server. I need to use Data Factory and it will be triggered from my service so I will not have access while it will run, I need everything to be automatic.

I can't do export because I can't approve manually the endpoints.

I tried to do copy DB with T-SQL query , but I can't do it because of the private endpoint.

Data factory copies only the data, So I need to run scripts to copy all the metadata.

I ran select * from sys.objects but I'm not getting the indexes so I'm afraid I'm missing something but I see many objects so it helps, but I see objects of "SYSTEM_TABLE" I'm not sure why.

  1. How can I know what should I copy? How can I know I'm not missing anything?
  2. Do I need a script for every object type?
  3. Maybe there is a way that I missed?
Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amira Bedhiafi 20,176 Reputation points
    2024-02-28T13:00:39.7233333+00:00

    You need to understand that your database consists of several types of objects, including but not limited to:

    • Tables and their data
    • Indexes
    • Stored procedures
    • Views
    • User-defined functions
    • Schemas
    • User roles and permissions

    Each of these objects is important for the proper functioning of your applications that rely on the database. Since the limitations you mentioned, you'll need to script out your database objects and execute these scripts on the target server. With SSMS, you can use "Generate Scripts" wizard which allows you to script out the entire database or specific objects within it and you can automate this process by using the sqlpackage.exe command-line utility or PowerShell scripts. Another alternative can be PowerShell scripts can use the sqlpackage.exe tool for extracting a Data-tier Application (DAC) package from the source database. The DAC package includes schema and table data, which can then be deployed to the target SQL Server. While ADF can't directly script database objects, you can execute pre-scripting steps to prepare the target database before using ADF to move data. Another detail, since you're dealing with private endpoints, ensure that the network security allows for connectivity between your source database, the machine running the scripts, and the target database.

  2. ZoeHui-MSFT 35,556 Reputation points
    2024-02-29T01:40:49.87+00:00

    Hi @OGINTZ Marina,

    Database copy using T-SQL isn't supported when connecting to the destination server over a private endpoint. If a private endpoint is configured but public network access is allowed, database copy is supported when connected to the destination server from a public IP address using SQL authentication. Once the copy operation completes, public access can be denied.

    Follow this documentation to see if it what you want.

    Copy a transactionally consistent copy of a database in Azure SQL Database

    Regards， Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

  3. Olaf Helper 43,246 Reputation points
    2024-02-29T06:26:02.6+00:00

    My preferred way is a SSDT = "SQL Server Data Tools" with a database project, where you can import a database model completely, may make changes and deploy it to anywhere, see https://learn.microsoft.com/en-us/sql/ssdt/how-to-create-a-new-database-project?view=sql-server-ver16

  4. AnnuKumari-MSFT 32,161 Reputation points Microsoft Employee
    2024-03-06T08:24:55.9066667+00:00

    Hi OGINTZ Marina ,

    As mentioned by Amira , ADF can't generate the script for SQL objects , however if you already have scripts for objects, you can perform data migration using ADF.

    You can script the database schema and data using SSMS

    Right click on the database then choose generate sql scripts under tasks.

    Specify all objects. Then in the options menu choose at the bottom to generate scripts for data.

    Below are some links that will be helpful:

    http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

    http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

    Since you mentioned that having private endpoint is the reason you are not able to export the script, you can try removing the endpoint connection before generating the sql scripts : https://learn.microsoft.com/en-us/azure/private-link/manage-private-endpoint?tabs=manage-private-link-powershell#remove-a-private-endpoint-connection

    Hope it helps . Thankyou

    0 comments