The Backstory
Im inheriting a toolbox of projects from one of our older devs who's retiring at the end of the month, I come from the land of Linux, MongoDB and MySQL, so SQLServer is new to me... This dev was an old school guy and didn't really use best practices. A lot of his dev work was done with the production server (gulp) with VB6 which aged out in 1999. The guy was the head of department running a warehouse, so best practices weren't really a big deal to him unfortunately.
That being said, Im in the process of building my dev environment and severing all connections with live data. My IT Department has setup a system of making backups, which I can ask for access to, but I think my inherited credentials will allow me to make backups.
TLDR - The Question
Since i do not have direct access to the host computer, and I cannot install any CLI tools on the host machine, I came across this blog Backing up a SQL database to Amazon S3 using Powershell and was reading this Microsoft article Full backup to Azure and especially the Azure example got me to wonder is it possible to write a powershell script on my OS, trigger a backup to build a file on host OS, and then move the file off to Azure without installing any CLI tools? If so thats scary, but I might also consider it in order to get a copy of the database I need.
Edit
Sorry, I should make it clearer... I do have written permission, and have been working with my IT department on this topic already. They are fully aware of what I am doing, I simply encountered a roadblock while attempting to make my backup.
I will give some code samples below. I am also one of the security officers and a senior developer for the IT department (but part of our development arm) so formally we are two separate departments but informally I have full authority to poke around and look for security concerns. If the answer is "not possible" then thats a good thing, and I will go get my backups directly from the actual machine hosting our SQL-Server on premises.
that said:
I am working from my workstation, running PowerShell scripts from my work station.
$server = "THESERVER"
$db = "THEDB"
$datetime = (Get-Date).ToString('MM-dd-yyyy-hh-mm-ss-tt');
$database1 = 'C:\temp\' + $db + '_' + $datetime + '.back';
$creds = Get-Credential 'admin'
Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile $database1 -Credential $creds;
this reports an error:
Backup-SqlDatabase : System.Data.SqlClient.SqlError: Cannot open backup device
'C:\temp\THEDB_07-20-2021-04-14-06-PM.back'. Operating system error 3(The system cannot find the path specified.).
At C:\Users\alilland\Documents\dev\bin\sql-server\db_backup.ps1:7 char:1
+ Backup-SqlDatabase -ServerInstance $server -Database $db -BackupFile ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], SmoException
+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.BackupSqlDatabaseCommand
this error is taking place because my filepath is a local filepath to my machine, not a filepath that exists on the host of the sql-server, which fully makes sense, I completely understand why that error is taking place.
While looking at the docs I came across the following PowerShell script for backing up a SqlDatabase and uploading to azure. It appears that I can pass in an azure container from my workstation, where I wouldnt need to be logged in to the host machine in order to make it work.
$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName
Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential
before I go through the hassle of setting up an azure container with permissions, I know I can get the script to successfully run on the host, but will Backup-SqlDatabase accept an azure container path and upload to azure from my workstation?