{"id":228,"date":"2016-01-13T15:24:12","date_gmt":"2016-01-13T13:24:12","guid":{"rendered":"http:\/\/ingeniarius.net\/blog\/?p=228"},"modified":"2022-08-25T17:21:46","modified_gmt":"2022-08-25T15:21:46","slug":"a-script-to-export-all-sql-azure-databases","status":"publish","type":"post","link":"https:\/\/ingeniarius.net\/blog\/2016\/01\/a-script-to-export-all-sql-azure-databases\/","title":{"rendered":"A script to export sql azure databases."},"content":{"rendered":"<p>My context for the script in this case.<\/p>\n<ol>\n<li>A bug from the client is reported. A bug that does not cause exception at the back-end nor at the front-end &#8211; 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 ).<\/li>\n<li>You need to dump the database from the staging environment ( NO! Not the production! )<\/li>\n<\/ol>\n<p>The possible solutions :<\/p>\n<ol>\n<li>Go to azure portal and export all the databases needed by hand &#8211; click-click-click&#8230;.a lot.<\/li>\n<li>Make a powershell script and do it really quick. This helps a lot when you do it more then once.<\/li>\n<\/ol>\n<p>The script :<\/p>\n<p><!--more--><\/p>\n<pre class=\"lang:ps decode:true\" title=\"Powershell script to export Sql Azure Database\"># The command will prompt only once for most parameters during one session\nparam(\n[string] $azSubscriptionId, \n[string] $azStorageName, \n[string] $azStorageKey, \n[string] $azContainerName,\n[string] $azSqlServerName, \n[string] $azSqlServerUserName, \n[string] $azSqlServerPassword)\n\n\n \n#if you want to clean up all globally created vars =&gt; Remove-Variable -Name 'az*'\n Remove-Variable -Name 'az*'\n\n$azSubscriptionId = \"...\"\n$azStorageName = \"...\"\n$azStorageKey = \"...\"\n$azContainerName = \"...\"\n$azSqlServerName = \"...\"\n$azSqlServerUserName = \"...\"\n$azSqlServerPassword = \"...\"\n \n#Remove-Variable -Name 'azAccount' # clean up acc credentials. Can be removed but once in a while the credentials for the account get expired\n \n#region azAccount\n$azAccount = Get-AzureAccount\nif(!$azAccount)\n{\n    Do\n    {\n       Write-Output \"No azure account. Prompting ...\"\n       $azAccount = Add-AzureAccount\n    } While (!$azAccount)\n    Set-Variable -Name azAccount -Value $azAccount -Scope Global\n    $azAccount = Get-AzureAccount\n}\n#endregion\n \n#region azSubscriptionId\nif(!$azSubscriptionId) \n{\n    Do\n    {\n        Write-Output \"No subscription Id. Prompting ...\"\n        $azSubscriptionId = Read-Host \"Subscription Id\"\n    } While (!$azSubscriptionId)\n    Write-Output \"Input for subscription Id $azSubscriptionId\"\n    Set-Variable -Name azSubscriptionId -Value $azSubscriptionId -Scope Global\n}\nSelect-AzureSubscription -SubscriptionId  $azSubscriptionId\nWrite-Output \"Selected subscription Id $azSubscriptionId\"\n#endregion\n \n#region azStorageName\nif(!$azStorageName) \n{\n    Do\n    {\n        Write-Output \"No storage name. Prompting ...\"\n        $azStorageName = Read-Host \"storage name\"\n    } While (!$azStorageName)\n    Set-Variable -Name storageName -Value $azStorageName -Scope Global\n}\n#endregion\n \n#region azStorageKey\nif(!$azStorageKey) \n{\n    Do\n    {\n        Write-Output \"No storage key. Prompting ...\"\n        $azStorageKey = Read-Host \"storage key\"\n    } While (!$azStorageKey)\n    Set-Variable -Name azStorageKey -Value $azStorageKey -Scope Global\n}\n#endregion\n \n#region azContainerName\nif(!$azContainerName) \n{\n    Do\n    {\n        Write-Output \"No container name. Prompting ...\"\n        $azContainerName = Read-Host \"container name\"\n    } While (!$azContainerName)\n    Set-Variable -Name azContainerName -Value $azContainerName -Scope Global\n}\n#endregion\n \n#region azSqlServerName\nif(!$azSqlServerName) \n{\n    Do\n    {\n        Write-Output \"No sql server name. Prompting ...\"\n        $azSqlServerName = Read-Host \"sql server name\"\n    } While (!$azSqlServerName)\n    Set-Variable -Name azSqlServerName -Value $azSqlServerName -Scope Global\n}\n#endregion\n \n#region azSqlServer\nif(!$azSqlServer) \n{   \n    Write-Output \"No sql server variable stored on this PC. Prompting ...\"\n    $azSqlServer = Get-AzureSqlDatabaseServer -ServerName $azSqlServerName \n    Set-Variable -Name azSqlServer -Value $azSqlServer -Scope Global\n}\n#endregion\n \n#region azSqlServerCredenials\nif(!$azSqlServerCredenials) \n{\n    Do\n    {\n        Write-Output \"No sql server credentials. Prompting ...\"\n        $azSqlServerCredenials = Get-Credential \"Enter Credentials for $azSqlServerName\"\n    } While (!$azSqlServerCredenials)\n    Set-Variable -Name azSqlServerCredenials -Value $azSqlServerCredenials -Scope Global\n}\n#endregion\n \n#region Sql connection context\nif(!$azSqlCtx)\n{\n    Write-Output \"No Sql Server Context. Creating...\"\n    $azSqlCtx = New-AzureSqlDatabaseServerContext -ServerName $azSqlServer.ServerName -Credential $azSqlServerCredenials\n    Set-Variable -Name azSqlCtx -Value $azSqlCtx -Scope Global\n     Write-Output \"Sql Server Context for $azSqlServer.ServerName created.\"\n}\n \n#endregion\n \n#region Storage connection context\nif(!$azStorageCtx)\n{\n    Write-Output \"No Storage Context. Creating...\"\n    $azStorageCtx = New-AzureStorageContext -StorageAccountName $azStorageName -StorageAccountKey $azStorageKey\n    Set-Variable -Name azStorageCtx -Value $azStorageCtx -Scope Global\n    Write-Output \"Storage context for $azStorageName created.\"\n}\n#endregion\n \n#region Storage container ref\nif(!$azStorageContainer)\n{\n    Write-Output \"No container ref. Creating for $azContainerName\"\n    $azStorageContainer = Get-AzureStorageContainer -Name $azContainerName -Context $azStorageCtx\n    Set-Variable -Name azStorageContainer -Value $azStorageContainer -Scope Global\n    Write-Output \"Container ref to $azStorageContainer created.\"\n}\n#endregion\n \n#region Sql Databases ref\nif(!$azSqlServerDatabases)\n{\n    Write-Output \"No Sql Databases array ref. Creating...\"\n\n    #$azSqlServerDatabases = Get-AzureSqlDatabase -ConnectionContext $azSqlCtx\n#because from some time on the first way of gettign the dbs started to throw exceptions for \n#no apparent reason\n#use this\n   \n# or this \n    #$azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServer.ServerName\n# or this \n    $azSqlServerDatabases = Get-AzureSqlDatabase -ServerName $azSqlServerName\n\n\n    Set-Variable -Name azSqlServerDatabases -Value $azSqlServerDatabases -Scope Global\n    Write-Output \"Sql Databases array ref created.\"\n}\n#endregion\n \n#region actual export of azure databases\n$exportRequests = New-Object 'System.Collections.Generic.List[Microsoft.WindowsAzure.Commands.SqlDatabase.Model.SqlDatabaseServerOperationContext]'\n \nWrite-Output \"Exporting databases\"\nforeach ($db in $azSqlServerDatabases) {\n    $exportRequest = Start-AzureSqlDatabaseExport -SqlConnectionContext $azSqlCtx -StorageContainer $azStorageContainer -DatabaseName $db.Name -BlobName ($db.Name + \".bacpac\") \n    $exportRequests.Add($exportRequest)\n    Write-Output ($db.Name + \".bacpac\") \n}\n#endregion\n#import dbs back into your local server\n\n#cd [FOLDERPATH]\n#$goodlist = dir\n# probably the correct path\n##C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\Common7\\IDE\\Extensions\\Microsoft\\SQLDB\\DAC\\120\n#cd 'C:\\Program Files (x86)\\Microsoft SQL Server\\110\\DAC\\bin'\n#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] }\n<\/pre>\n<p>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.<\/p>\n<p>**UPDATE<\/p>\n<p>I started to encouter strange but persistent error :<\/p>\n<blockquote><p>PS C:\\Windows\\system32&gt; Get-AzureSqlDatabase -ConnectionContext $azSqlCtx<br \/>\nWARNING: Client Session Id: &#8216;&#8230;&#8217;<br \/>\nWARNING: Client Request Id: &#8216;&#8230;&#8217;<br \/>\nGet-AzureSqlDatabase : Internal .Net Framework Data Provider error 6.<br \/>\nAt line:1 char:1<br \/>\n+ Get-AzureSqlDatabase -ConnectionContext $azSqlCtx<br \/>\n+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br \/>\n+ CategoryInfo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : NotSpecified: (:) [Get-AzureSqlDatabase], InvalidOperationException<br \/>\n+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.SqlDatabase.Database.Cmdlet.GetAzureSqlDatabase<\/p><\/blockquote>\n<p>There is an article about this error <a href=\"https:\/\/support.microsoft.com\/en-us\/kb\/944099\">here<\/a> but I did not find any way to download the fix. Than I opened msdn where GetAzureSqlDatabase is described and used the &#8220;ByServerName&#8221; method.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8211; 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 (&hellip; <a class=\"more-link\" href=\"https:\/\/ingeniarius.net\/blog\/2016\/01\/a-script-to-export-all-sql-azure-databases\/\">Continue reading <span class=\"screen-reader-text\">A script to export sql azure databases.<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,19],"tags":[18,20,21,22],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/posts\/228"}],"collection":[{"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/comments?post=228"}],"version-history":[{"count":7,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/posts\/228\/revisions"}],"predecessor-version":[{"id":444,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/posts\/228\/revisions\/444"}],"wp:attachment":[{"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/media?parent=228"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/categories?post=228"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ingeniarius.net\/blog\/wp-json\/wp\/v2\/tags?post=228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}