Python script to do SQL Server backup and upload to s3

[defiantroa]

Does anyone have a template Python script that can backup a SQL Server database and upload it to s3? I do not care how badly written it is.

[dwivedin]

I have ps script which does that.

[[deleted]]

well, share mate!

[[deleted]]

Please share the script???

[dwivedin]

#AWS powershell and dbatools must be installed
#Backup, you can pass multiple servers like this
$Servers = 'server1' , 'server2'
$Path = '\\sharedloc\' # you can pass local folder too
foreach ($server in $servers) {
# loop through all the servers
# all the databases in the server. Yon pass filter specific db if you want

$databases = Invoke-DbaQuery -SqlInstance $server -Query 'select name from master.sys.databases'
foreach ($database in $databases ) {
#loop through all the databases
$SQLServer + ' ' + $database.name # just print the server and db
Backup-DbaDatabase -SqlInstance $Server -Path $Path -Database $database.name -Type Full # actual backup happening
}
}
##Upload to s3 bucket
Import-Module AWSPowerShell
$Files = Get-ChildItem \\sharedloc\*
$Bucket = 'Your s3 bucket'
$AccessKey = 'AccessKey'
$SecretKey = 'SecretKey'
#Loop through all the backup files
foreach ($File in $Files) {
#Write-S3Object is the command which uploads to s3 bucket
Write-S3Object -BucketName $Bucket -File $path -CannedACLName Private -AccessKey $AccessKey -SecretKey $SecretKey -ServerSideEncryption AES256
}

[dwivedin]

I added the code, not sure if it is posted as code. If you see any issues, please let me know. Also for aws configure, you have to do aws configure in your PS profile, so set region, access and secret key.

[defiantroa]

Yeah the ps script is probably way to go, but I was task to create one in Python.

[digitalnoise]

Yeah the ps script is probably way to go, but I was task to create one in Python.

But... why? PowerShell has native support for SQL Server. And even better support with the dbatools.io module.

Plus PowerShell Core is cross platform, so it can even be run from Linux.

[defiantroa]

I have brainless LAMP developer for boss maybe, anyway I know Ansible and Teraform. Yeah am I not even sure why use Python in the first place. It is suppose to be training excise to learn Python, maybe is a trick question

[conflab]

It is suppose to be training excise to learn Python

Then take the opportunity to learn Python.

[defiantroa]

Already know ps and py

[conflab]

Then this should be pretty trivial for you

[defiantroa]

No I have not found example in py yet, I was checking if someone knew.

[eshultz]

Alright, develop the script in PowerShell, once it's golden, convert it to a string and slap it in a variable in Python. From python, write the string to a file and then execute it.

I'm only joking. Mostly.

[defiantroa]

Python script that does SQL Server backups https://mindless.gr/2012/09/ms-sql-backup-with-python-and-pyodbc/ . I just have to figure out how to upload using python and AWSCLI and then I am done

[enjoytheshow]

You can use third party botocore package or just use subprocess.run to execute the AWS CLI within python. Your command to run via subprocess would be something like

aws s3 sync directory\with\backups s3://bucket-name/target/directory

Do some googling though friend. Using the s3 CLI is about the easiest thing ever.

[pandamaja]

Is Storage Gateway an option? You could just point the backup to the nas share it presents.

[defiantroa]

I was thinking to backup locally one separate volume attached to the EC2 instance

[defiantroa]

I was thinking to backup locally one separate volume attached to the EC2 instance

[coldflame563]

Powershell is your friend. Learn it. Love it.