How to copy database within elastic pool using PowerShell

Question

Could you please advise the PowerShell code which copies one database into another within the same elastic pool?

I was trying to use

1. New-AzureRmSqlDatabaseCopy, but the execution failed because New-AzureRmSqlDatabaseCopy is not intended to work with the elastic pools.
2. How to Execute and Store Transact-SQL (T-SQL) Query Results in a PowerShell Array, but it errorred out with the time-out exception.
3. Invoke-Sqlcmd also failed with Invoke-Sqlcmd : Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding exception.

Here is what I need:

1. Copy the database within the same elastic pool. I use "CREATE DATABASE [Database_Name_02] AS COPY OF [Database_Name_01]" T-SQL script when I do it manually.
2. Obtain notification when it is copied (it takes about 5-7 mins to copy) or error message when it fails.

Thank you very much for your help, in advance!!!

Answer 1

I think New-AzureRmSqlDatabaseCopy will work fine, if you want to copy the database within an elastic pool, the database and its copy should be in the same sql server.

You could try the command below, it works fine on my side.

New-AzureRmSqlDatabaseCopy -ResourceGroupName <ResourceGroupName> -ServerName <ServerName> -DatabaseName <databaseName of the db to be copied> -CopyDatabaseName <databaseName of the copy> -ElasticPoolName <ElasticPoolName>

Check in the portal: