question

AronLilland-9833 avatar image
0 Votes"
AronLilland-9833 asked ErlandSommarskog answered

Is it possible to take a backup and upload to Azure without having access to the host machine?

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?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @AronLilland-9833,

Make sure your account has the permission to do a SQL server backup. BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Did you want to backup database to Azure Blob Storage? Using T-SQL query or powershell ? If you want to get detail steps about this, read below blog.

Quickstart: SQL backup and restore to Azure Blob storage service

SQL Server backups to Azure Blob storage

If I misunderstood, please let me know.

Since i do not have direct access to the host computer

What did you mean? Your account do not have the permission to access the local server?


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered AronLilland-9833 commented

I am not sure that I understand what really want to achieve. You can run BACKUP to URL from inside SQL Server, but if the aim is to get a copy of the production database on your laptop, you should absolutely work with your IT department to do that.

What if they say no, and you are able to find a sneaky way to still do that? That could certainly be reason for terminating your employment contract, if this is uncovered.

And mind you, IT may not say completely no, but there could be regulations that says that data has to be anonymised or masked in some way.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I went ahead and added more clarity

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

In the end, the PowerShell script invokes the BACKUP command in SQL Server, and the service account for SQL Server will need permissions wherever you are writing to. For backing up to Azure, there is a specific syntax TO URL, and I believe that you may have to set up credentials for that to work. I'm fairly vague, since I have not engaged in this myself.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.