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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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