First published on MSDN on Dec 12, 2017
Scenario:
when your test environment need a fresh copy of the data from production or any other scenario when fresh data is needed on another database you might find yourself seeking a quick and easy solution.
Solution Using TSQL:
when you connect to any of the databases on the server you can copy the database to another database on the same server.
[code language="sql"]CREATE DATABASE [ProdDB_TSQLFresh] AS COPY OF ProdDB;
DROP DATABASE ProdDB_TSQLCopy;
ALTER DATABASE ProdDB_TSQLFresh MODIFY NAME = ProdDB_TSQLCopy; [/code]
Solution Using PowerShell:
[code language="PowerShell"]"Starting..."
"Removing old copy..."
Remove-AzureRmSqlDatabase -ResourceGroupName "ResourceGroupName" -ServerName "ServerName" -DatabaseName "DatabaseName" -Force
"Start Copy... Please wait ... "
New-AzureRmSqlDatabaseCopy -ResourceGroupName "ResourceGroupName" `
-ServerName "ServerName" `
-DatabaseName "SourceDatabaseName" `
-CopyResourceGroupName "DestinationResourceGroupName" `
-CopyServerName "DestinationServerName" `
-CopyDatabaseName "DestinationDatabaseName"
" !Done! "[/code]
Automation:
How to automate the process to run on a regular basis?
we recommend using Azure Automation to schedule these commands to run.
follow the instructions
here
to run it as a PowerShell script or
Invoke-SQLCMD
to run the T-SQL statements
if you choose the PowerShell option use this piece of code to authenticate with your run-as account:
(This code is in your newly created automation account in the samples runbooks)
[code language="PowerShell"]$connectionName = "AzureRunAsConnection"
try
{
# Get the connection "AzureRunAsConnection "
$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName
"Logging in to Azure..."
Add-AzureRmAccount `
-ServicePrincipal `
-TenantId $servicePrincipalConnection.TenantId `
-ApplicationId $servicePrincipalConnection.ApplicationId `
-CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
}
catch {
if (!$servicePrincipalConnection)
{
$ErrorMessage = "Connection $connectionName not found."
throw $ErrorMessage
} else{
Write-Error -Message $_.Exception
throw $_.Exception
}
}[/code]
