| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Microsoft Azure SQL Database Copy Operation
by BF (Principal Consultant; Architecture; Engineering)


Powershell Code:

Authenticate to Microsoft Azure Portal:

Select your Subscription(Environment):
Select-AzureRmSubscription -SubscriptionName "XYZ"

Main Code:

#Section 1: Define Variables
$SourceServer = "azr-srv01-nyc"
$SourceDB = "INVOICES"
$PartnerServer = "azr-srv02-nyc" #This server must be in the same Azure subscription as the source
$PartnerDB = "INVOICESCopy"

#Section 2: Create a Function to calculate in-progress copy time. It also outputs any previously completed copy tasks.
Function GetAzureSqlDatabaseCopyStatus

# Monitor the status of the copy. Execute on the SourceServer and Source DB.
$CopyStatusObject = Get-AzureSqlDatabaseOperation -ServerName $SourceServer -DatabaseName $SourceDB

Foreach ($i in $CopyStatusObject | Sort-Object StartTime -Descending)
If ($i.State -like "IN_PROGRESS")
$CurrentTime = Get-Date -format HH:mm:ss
$Time1 = NEW-TIMESPAN -start $i.StartTime -End $i.LastModifyTime
$Time2 = NEW-TIMESPAN -start $StartTimeActual -End $CurrentTime
$MSG1 = "INPROGRESS: Start-AzureSqlDatabaseCopy: Current Copy Time based on Azure: " + $Time1.TotalSeconds + " secs; (" + $i.PercentComplete + "% Complete)"
$MSG2 = "INPROGRESS: Start-AzureSqlDatabaseCopy: Current Copy Time based on PS: " + $Time2.TotalSeconds + " secs"

If ($i.State -like "COMPLETED")
$Time1 = $i.StartTime
$Time2 = $i.LastModifyTime
$MSG1 = "COMPLETED : Start-AzureSqlDatabaseCopy: " + "StartTime: " + $Time1 + ", LastModifyTime: " + $Time2

#Section 3: Start Azure SQL Database Copy
#Copy Azure SQL Database to a different server (remove the -PartnerServer parameter to copy to the same server)
Start-AzureSqlDatabaseCopy -ServerName $SourceServer -DatabaseName $SourceDB -PartnerServer $PartnerServer -PartnerDatabase $PartnerDB
$StartTimeActual = Get-Date -format HH:mm:ss

#Section 4: Call Function


Start-AzureSqlDatabaseCopy - If you do not specify the ContinuousCopy parameter, this cmdlet creates a one-time copy of the source database

If you delete the Azure SQL Database copy, the LastModifyTime will be changed to that time. Thus, the LastModifyTime may not be accurate for getting only Copy time - for completed tasks.


Microsoft Azure