Home
%3CLINGO-SUB%20id%3D%22lingo-sub-368960%22%20slang%3D%22en-US%22%3ECopy%20production%20database%20to%20stage%20or%20test%20environment%20using%20PowerShell%20or%20TSQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-368960%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Dec%2012%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Scenario%3A%20%3C%2FSTRONG%3E%20when%20your%20test%20environment%20need%20a%20fresh%20copy%20of%20the%20data%20from%20production%20or%20any%20other%20scenario%20when%20fresh%20data%20is%20needed%20on%20another%20database%20you%20might%20find%20yourself%20seeking%20a%20quick%20and%20easy%20solution.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3ESolution%20Using%20TSQL%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20when%20you%20connect%20to%20any%20of%20the%20databases%20on%20the%20server%20you%20can%20copy%20the%20database%20to%20another%20database%20on%20the%20same%20server.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22sql%22%5DCREATE%20DATABASE%20%5BProdDB_TSQLFresh%5D%20AS%20COPY%20OF%20ProdDB%3B%20%3CBR%20%2F%3E%20DROP%20DATABASE%20ProdDB_TSQLCopy%3B%20%3CBR%20%2F%3E%20ALTER%20DATABASE%20ProdDB_TSQLFresh%20MODIFY%20NAME%20%3D%20ProdDB_TSQLCopy%3B%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Solution%20Using%20PowerShell%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22PowerShell%22%5D%22Starting...%22%20%3CBR%20%2F%3E%20%22Removing%20old%20copy...%22%20%3CBR%20%2F%3E%20Remove-AzureRmSqlDatabase%20-ResourceGroupName%20%22ResourceGroupName%22%20-ServerName%20%22ServerName%22%20-DatabaseName%20%22DatabaseName%22%20-Force%20%3CBR%20%2F%3E%20%22Start%20Copy...%20Please%20wait%20...%20%22%20%3CBR%20%2F%3E%20New-AzureRmSqlDatabaseCopy%20-ResourceGroupName%20%22ResourceGroupName%22%20%60%20%3CBR%20%2F%3E%20-ServerName%20%22ServerName%22%20%60%20%3CBR%20%2F%3E%20-DatabaseName%20%22SourceDatabaseName%22%20%60%20%3CBR%20%2F%3E%20-CopyResourceGroupName%20%22DestinationResourceGroupName%22%20%60%20%3CBR%20%2F%3E%20-CopyServerName%20%22DestinationServerName%22%20%60%20%3CBR%20%2F%3E%20-CopyDatabaseName%20%22DestinationDatabaseName%22%20%3CBR%20%2F%3E%20%22%20!Done!%20%22%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Automation%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20How%20to%20automate%20the%20process%20to%20run%20on%20a%20regular%20basis%3F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20we%20recommend%20using%20Azure%20Automation%20to%20schedule%20these%20commands%20to%20run.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20follow%20the%20instructions%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fautomation%2Fautomation-first-runbook-textual-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20here%20%3C%2FA%3E%20to%20run%20it%20as%20a%26nbsp%3BPowerShell%20script%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fpowershell%2Finvoke-sqlcmd-cmdlet%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Invoke-SQLCMD%20%3C%2FA%3E%20to%20run%20the%20T-SQL%20statements%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20if%20you%20choose%20the%20PowerShell%20option%20use%20this%20piece%20of%20code%20to%20authenticate%20with%20your%20run-as%20account%3A%20%3CBR%20%2F%3E%20(This%20code%20is%20in%20your%20newly%20created%20automation%20account%20in%20the%20samples%20runbooks)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22PowerShell%22%5D%24connectionName%20%3D%20%22AzureRunAsConnection%22%20%3CBR%20%2F%3E%20try%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%23%20Get%20the%20connection%20%22AzureRunAsConnection%20%22%20%3CBR%20%2F%3E%20%24servicePrincipalConnection%3DGet-AutomationConnection%20-Name%20%24connectionName%20%3CBR%20%2F%3E%20%22Logging%20in%20to%20Azure...%22%20%3CBR%20%2F%3E%20Add-AzureRmAccount%20%60%20%3CBR%20%2F%3E%20-ServicePrincipal%20%60%20%3CBR%20%2F%3E%20-TenantId%20%24servicePrincipalConnection.TenantId%20%60%20%3CBR%20%2F%3E%20-ApplicationId%20%24servicePrincipalConnection.ApplicationId%20%60%20%3CBR%20%2F%3E%20-CertificateThumbprint%20%24servicePrincipalConnection.CertificateThumbprint%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20catch%20%7B%20%3CBR%20%2F%3E%20if%20(!%24servicePrincipalConnection)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20%24ErrorMessage%20%3D%20%22Connection%20%24connectionName%20not%20found.%22%20%3CBR%20%2F%3E%20throw%20%24ErrorMessage%20%3CBR%20%2F%3E%20%7D%20else%7B%20%3CBR%20%2F%3E%20Write-Error%20-Message%20%24_.Exception%20%3CBR%20%2F%3E%20throw%20%24_.Exception%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%5B%2Fcode%5D%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-368960%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2012%2C%202017%20Scenario%3A%20when%20your%20test%20environment%20need%20a%20fresh%20copy%20of%20the%20data%20from%20production%20or%20any%20other%20scenario%20when%20fresh%20data%20is%20needed%20on%20another%20database%20you%20might%20find%20yourself%20seeking%20a%20quick%20and%20easy%20solution.%3C%2FLINGO-TEASER%3E
Microsoft
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]