Copy database from another with Managed Instance

Question

Copy database from another with Managed Instance

Atat Rowcount

Default port

Points: 1453
More actions
July 21, 2021 at 3:27 pm

#3912502

Hi
I have the need to programmatically create a new database as a copy of an existing one, within the same Azure Managed Instance. I have searched for hours and cannot find a way to do it! This need will arise frequently, hence the need to do it programmatically.
I presume it has to be possible. Any ideas?
@@

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Do you mean a copy as of a point in time? Typically this is the same as a restore of a backup of one database. Typically you would backup and restore, which IIRC, can be done in code.

Atat Rowcount Default port Points: 1453 More actions · Answer 2

Thanks Steve. Yes that sounds as if it would work, but from the little I know of Azure MI I don't have access to the filesystem, how can I backup and restore in code without this?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

There are various APIs: https://docs.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups

I might think about Az or PoSh here to automate this and get a copy at a point in time.

Atat Rowcount Default port Points: 1453 More actions · Answer 4

Thanks Steve. A web application (running on a VM in Azure) will as part of customer registration (frequently), make the decision to create a new database for that customer. I'm not sure PowerShell is the way I want to go here, ideally there would be a way from ASP.NET to call something in SQL Server to create the database using another a template (presumably by doing an on-the-spot backup and restore of the template database). I also need to be careful which permissions I grant the application user (it usually only has permission to execute, so I need to expand on that). I'll likely need to involve the .NET guys in this because that side of things isn't my area, I was hoping there was something reasonably straightforward in Azure SQL MI I could point them to.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

There is a REST API, but as you mentioned, likely this is a permissions issue you don't want to grant.

Typically what I'd do here, since anything that is direct requires permissions, is to use the app to set a flag somewhere. Easy for a 1 row/1col table. Then some app polls this regularly and when the flag is set, it does something. An Azure Function or some other automation could do this easily.

Atat Rowcount Default port Points: 1453 More actions · Answer 6

Ohhh... that's an excellent idea, thanks! Yes I'll get the devs to call a proc that records that it needs to be done and I'll have it picked up on the DB end automatically. Result! Thank you!

Michael L John One Orange Chip Points: 27038 More actions · Answer 7

Have you looked at this?

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=azuresqldb-current&preserve-view=true&tabs=sqlpool#copy-a-database

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/