Forum Discussion
dbmsoutput
Jan 07, 2024Copper Contributor
Copy schema (with data) from one Azure SQL database to another Azure SQL database
Hallo,
We need copy 3 schemas (incl tables, indexes, keys and data) to another Azure SQL database.
We decided to switch from 2 database to one more powerful. There is a lot of data, smallest database is about 1TB data. I tried do it SSMS or BCP on client VM but it does not work due amount of data and transfer speed.
I am looking something to do this in Azure portal. Both Azure databases are on one Azure host.
Current situation:
- database source A_DB schemas c11, c12, c13; about 1TB data in 300 tables
- database target B_DB schemes c21, c22, c23, c24, c25; Hyperscale and 3TB data
Desired result:
- target B_DB after copying the schema consists from schemas with the date c11, c12, c13, c21, c22, c23, c24,
- database A_DB can be deleted
Please advise what azure service or tool can i use for it. It's a simple task in Oracle but it look more complicated in Azure SQL environment. So far I have found how to exp/imp or backup/restore an entire database, but nothing about how i can import schema with a lot of data in Azure environment.
Thank you
- Maxim ICopper ContributorJust copying the whole database to a new one via azure portal and drop unnecessary schemas.
- Hello,
You can use SQL Server Management Studio Import Export
Or you can use Azure Data Factory Pipelines
Both options will allow you to move schemas and data from source to destination
https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver16
https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipelines-activities?tabs=data-factory
Regards
Javier- dbmsoutputCopper Contributor
Javier_Villegas thank you.
SSMS and BCP are not real options, i tried but i need copy more than 1TB data. SSMS and BCP from cliënt are very slow. Both our databases are Azure SQL.
ADF i need investigate, didn't use it before. Is it puur Azure task and do you can easy select and copy full schema's or 300 tables at once?