My context for the script in this case.
- A bug from the client is reported. A bug that does not cause exception at the back-end nor at the front-end – The font-end simply stops. It does not even freeze. So you do not have any log for this case. Now you are in check ( chess check ).
- You need to dump the database from the staging environment ( NO! Not the production! )
The possible solutions :
- Go to azure portal and export all the databases needed by hand – click-click-click….a lot.
- Make a powershell script and do it really quick. This helps a lot when you do it more then once.
The script :
# The command will prompt only once for most parameters during one session
param(
[string] $azSubscriptionId,
[string] $azStorageName,
[string] $azStorageKey,
[string] $azContainerName,
[string] $azSqlServerName,
[string] $azSqlServerUserName,
[string] $azSqlServerPassword)
#if you want to clean up all globally created vars => Remove-Variable -Name 'az*'
Remove-Variable -Name 'az*'
$azSubscriptionId = "..."
$azStorageName = "..."
$azStorageKey = "..."
$azContainerName = "..."
$azSqlServerName = "..."
$azSqlServerUserName = "..."
$azSqlServerPassword = "..."
#Remove-Variable -Name 'azAccount' # clean up acc credentials. Can be removed but once in a while the credentials for the account get expired
#region azAccount
$azAccount = Get-AzureAccount
if(!$azAccount)
{
Do
{
Write-Output "No azure account. Prompting ..."
$azAccount = Add-AzureAccount
} While (!$azAccount)
Set-Variable -Name azAccount -Value $azAccount -Scope Global
$azAccount = Get-AzureAccount
}
#endregion
#region azSubscriptionId
if(!$azSubscriptionId)
{
Do
{
Write-Output "No subscription Id. Prompting ..."
$azSubscriptionId = Read-Host "Subscription Id"
} While (!$azSubscriptionId)
Write-Output "Input for subscription Id $azSubscriptionId"
Set-Variable -Name azSubscriptionId -Value $azSubscriptionId -Scope Global
}
Select-AzureSubscription -SubscriptionId $azSubscriptionId
Write-Output "Selected subscription Id $azSubscriptionId"
#endregion
#region azStorageName
if(!$azStorageName)
{
Do
{
Write-Output "No storage name. Prompting ..."
$azStorageName = Read-Host "storage name"
} While (!$azStorageName)
Set-Variable -Name storageName -Value $azStorageName -Scope Global
}
#endregion
#region azStorageKey
if(!$azStorageKey)
{
Do
{
Write-Output "No storage key. Prompting ..."
$azStorageKey = Read-Host "storage key"
} While (!$azStorageKey)
Set-Variable -Name azStorageKey -Value $azStorageKey -Scope Global
}
#endregion
#region azContainerName
if(!$azContainerName)
{
Do
{
Write-Output "No container name. Prompting ..."
$azContainerName = Read-Host "container name"
} While (!$azContainerName)
Set-Variable -Name azContainerName -Value $azContainerName -Scope Global
}
#endregion
#region azSqlServerName
if(!$azSqlServerName)
{
Do
{
Write-Output "No sql server name. Prompting ..."
$azSqlServerName = Read-Host "sql server name"
} While (!$azSqlServerName)
Set-Variable -Name azSqlServerName -Value $azSqlServerName -Scope Global
}
#endregion
#region azSqlServer
if(!$azSqlServer)
{
Write-Output "No sql server variable stored on this PC. Prompting ..."
$azSqlServer = Get-AzureSqlDatabaseServer -ServerName $azSqlServerName
Set-Variable -Name azSqlServer -Value $azSqlServer -Scope Global
}
#endregion
#region azSqlServerCredenials
if(!$azSqlServerCredenials)
{
Do
{
Write-Output "No sql server credentials. Prompting ..."
$azSqlServerCredenials = Get-Credential "Enter Credentials for $azSqlServerName"
} While (!$azSqlServerCredenials)
Set-Variable -Name azSqlServerCredenials -Value $azSqlServerCredenials -Scope Global
}
#endregion
#region Sql connection context
if(!$azSqlCtx)
{
Write-Output "No Sql Server Context. Creating..."
$azSqlCtx = New-AzureSqlDatabaseServerContext -ServerName $azSqlServer.ServerName -Credential $azSqlServerCredenials
Set-Variable -Name azSqlCtx -Value $azSqlCtx -Scope Global
Write-Output "Sql Server Context for $azSqlServer.ServerName created."
}
#endregion
#region Storage connection context
if(!$azStorageCtx)
{
Write-Output "No Storage Context. Creating..."
$azStorageCtx = New-AzureStorageContext -StorageAccountName $azStorageName -StorageAccountKey $azStorageKey
Set-Variable -Name azStorageCtx -Value $azStorageCtx -Scope Global
Write-Output "Storage context for $azStorageName created."
}
#endregion
#region Storage container ref
if(!$azStorageContainer)
{
Write-Output "No container ref. Creating for $azContainerName"
$azStorageContainer = Get-AzureStorageContainer -Name $azContainerName -Context $azStorageCtx
Set-Variable -Name azStorageContainer -Value $azStorageContainer -Scope Global
Write-Output "Container ref to $azStorageContainer created."
}
#endregion
#region Sql Databases ref
if(!$azSqlServerDatabases)
{
Write-Output "No Sql Databases array ref. Creating..."
#$azSqlServerDatabases = Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
#because from some time on the first way of gettign the dbs started to throw exceptions for
#no apparent reason
#use this
# or this
#$azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServer.ServerName
# or this
$azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServerName
Set-Variable -Name azSqlServerDatabases -Value $azSqlServerDatabases -Scope Global
Write-Output "Sql Databases array ref created."
}
#endregion
#region actual export of azure databases
$exportRequests = New-Object 'System.Collections.Generic.List[Microsoft.WindowsAzure.Commands.SqlDatabase.Model.SqlDatabaseServerOperationContext]'
Write-Output "Exporting databases"
foreach ($db in $azSqlServerDatabases) {
$exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $azSqlCtx -StorageContainer $azStorageContainer -DatabaseName $db.Name -BlobName ($db.Name + ".bacpac")
$exportRequests.Add($exportRequest)
Write-Output ($db.Name + ".bacpac")
}
#endregion
#import dbs back into your local server
#cd [FOLDERPATH]
#$goodlist = dir
# probably the correct path
##C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120
#cd 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin'
#foreach($i in $goodlist){ $name = $i.Name; $namer = $i.Name.Substring(0, $i.Name.length - 7); .\SqlPackage.exe /a:Import /sf:[FOLDERPATH]\$name /tdn:$namer /tsn:[SERVERNAME] }
This is it. If you can elaborate on it and make it automatically restore the dbs in SqlExpress, feel free to post it as a comment.
**UPDATE
I started to encouter strange but persistent error :
PS C:\Windows\system32> Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
WARNING: Client Session Id: ‘…’
WARNING: Client Request Id: ‘…’
Get-AzureSqlDatabase : Internal .Net Framework Data Provider error 6.
At line:1 char:1
+ Get-AzureSqlDatabase -ConnectionContext $azSqlCtx
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-AzureSqlDatabase], InvalidOperationException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.GetAzureSqlDatabase
There is an article about this error here but I did not find any way to download the fix. Than I opened msdn where GetAzureSqlDatabase is described and used the “ByServerName” method.
