Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



Microsoft Azure SQL Database Copy Operation
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-07-11








Solution:



Powershell Code:

Authenticate to Microsoft Azure Portal:
Login-AzureRmAccount

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"
$MSG1
$MSG2
}

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

#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
GetAzureSqlDatabaseCopyStatus





Notes:

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.



Resources:

Microsoft Azure

Start-AzureSqlDatabaseCopy