9 votes

RDS snapshot: how long does I/O suspension occur?

The answer comes from understanding how snapshotting works. At the start of a snapshot, a message (command) is sent to all applications to come to a consistent state and flush necessary data to disk. ...
  • 4,404
6 votes
Accepted

Is it possible to take a snapshot of a database in Azure?

Based on my knowledge, for now, Azure SQL database(Paas) does not support snapshot. There are many ways that you could choose to backup SQL database, please refer to this blog:Different ways to Backup ...
5 votes
Accepted

pg_dump compression; Server or Client side

The compression with -Fc (custom format) dumps is done client-side. Per the documentation: This format is also compressed by default There is no protocol compression done between client and server....
  • 10.6k
5 votes

Database backup encryption

Use gpg (GnuPG). You'll be able to generate multiple keys and encrypt a file for decryption with any key from chosen list. Also GPG keys are not symmetric — your server only need public keys for ...
  • 2,659
5 votes

How to use fsyncLock() safely in backing up a single mongod

What is the most safest backup solution for single mongod? Two options: Use mongodump against the running mongod. It won't block, but it'll cause some read locking and general noise as it backs up ...
  • 32.4k
5 votes
Accepted

SQL 2012 Transaction Log backup does not truncate the logs

Your transaction log backup is truncating the logs in the sense that it's making room in the existing log file for more transactions. If you want to shrink the log file, you need to choose the "shrink ...
4 votes

Will a Veeam 8 Snapshot Backup be be unable to restore my non-quiescable file-based databases if someone is logged in to them at the time?

Without application-level quiescing, your backups would be crash-consistent. The recovered VM would look like it had suddenly had its power cut off... For your purposes, it's important to understand ...
  • 196k
4 votes
Accepted

Automate pg_dump in a bash script

Not taking into account any specific syntax for pg_dump: #!/bin/bash $TODAY=`date --iso-8601` $BACKDIR=/backup pg_dump [options] > $BACKDIR/$HOSTNAME-$TODAY if [ "$?"-ne 0]; then echo "Help" |...
  • 97.6k
4 votes

How to do offsite AWS RDS PostgreSQL backups?

One option you have is to copy your RDS snapshots to another AWS account. This is a commonly-used practice. This can be done by: Sharing your RDS snapshot with your second AWS account. Copy ("pull") ...
  • 9,804
4 votes
Accepted

downloading a big database (postgres) to a local copy

9GB compressed dump isn't really that large. You just need to make do it right: dump from EC2 instance in the same AWS account and region that RDS database is running — not over the internet; use ...
  • 2,659
4 votes
Accepted

Where to store db backups in centos 7?

Put them wherever you'd like. As long as they're also copied elsewhere, preferably off-site. The only thing approaching a "best practice" here would be to put the backups on their own filesystem, so ...
  • 109k
4 votes

Is it possible to take a snapshot of a database in Azure?

UPDATE: In April 2020, Microsoft updated their documentation regarding Azure SQL's Database Copy feature to clarify that a database copy in Azure SQL is not an instantaneous snapshot. While the ...
  • 2,251
3 votes

What is the risk of a SQL Server backup strategy that is only weekly full backups and daily transaction log backups?

Their backup strategy shouldn't be driven by your opinion, it should be driven by their recovery point objective and recovery time objective. Have that discussion with the client and then implement a ...
  • 109k
3 votes
Accepted

Backup remote database using mysqldump

Give ssh the mysqldump command to run but don't redirect it to a file. That will bring stdout from the mysqldump back to your local machine where you can redirect that to a local file. ssh -i ...
3 votes

How to do offsite AWS RDS PostgreSQL backups?

I also wanted an offsite backup of my AWS RDS, and the code, logs, etc. I do this by: Doing a database export periodically (nightly at midnight, for me) into a given directory Having Attic Backup ...
  • 30.8k
3 votes

Automate pg_dump in a bash script

Here's what I came up with it's very simple but it works. Although I think Sven's answer does a better job with basic error handling. #!/bin/sh hostname=`hostname` # Dump DBs date=`date +"%Y%m%...
  • 1,201
3 votes

Ola Hallengren's SQL Backup to Network Location not working

I was diagnosing a similar issue implementing Ola's scripts. It would work on some servers and not others. I would get: "Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 395 XXX The ...
3 votes

best raid level for backup server

If you're going to use a hardware card, RAID 50 or 60 seems fine for this case. 10 is overkill. For storing a backup of living data (rather than something like tapes), I generally prefer the use of ...
  • 7,006
3 votes

Not able to backup of postgres DB from DBeaver

I did search and solution is like "which postgres-> and put result of it in it" Not on Ubuntu. which postgres will typically print nothing. The command that DBeaver wants are pg_dump to ...
2 votes
Accepted

Ola Hallengren's SQL Backup to Network Location not working

The issue has been resolved. I created a new network share location, giving it very open permissions (everyone:Full control), and the backup works to that location. Whatever the issue is, it must be ...
  • 388
2 votes

Ola Hallengren's SQL Backup to Network Location not working

Who is the job running as? Is it running as a domain user who has access to the shares in question? Or is it running as SA? If it's running as SA, it's running as your SQL Server Agent account. ...
2 votes

Can I restore the "mysql" system database along with another from a mysqldump backup?

The only way you could mysqldump a mysql system database from one MySQL instance into another without any issues is if the major versions of MySQL are the same. You can mysqldump MySQL 5.0.x systems ...
2 votes
Accepted

restore mongoDB databases from manual backup (not used mongodump): strange permission problems

If SELinux is enabled you can try stopping mongo and then running: restorecon -R /var/lib/mongodb* Maybe SELinux contexts were broken while transferring files.
  • 5,237
2 votes
Accepted

Will a Veeam 8 Snapshot Backup be be unable to restore my non-quiescable file-based databases if someone is logged in to them at the time?

In order to avoid such nightmare, you should use pre-freeze/post-thaw scripts to setup for job which backs this VM up. If needed, check related topics in Veeam forum to ask others about their ...
2 votes

How to Dump all databases from MySql server with mysqldump without passwords

Try this (from the mysql documentation): first create a config file for mysql and make sure that only your account has access to it: touch ~/.my.cnf chmod 600 ~/.my.cnf add the username password ...
  • 161
2 votes
Accepted

How to secure the db dump taken from daily run script

The Principle of least privilege suggests that the first thing you should do would be to remove any unnecessary privileges from the IAM user that is making the backups. The granularity of the s3:...
2 votes

AutoMySQLBackup: Create a single file for each database

You should be able to use the SEPDIR config parameter. It will create separate directories for each database, with an own file inside of it. From the Ubuntu manpage (but should work on CentOS just ...
  • 2,015
2 votes

Connect by mysqldump to a mysql server only allowing one ip

If you need that some specific user make backups remotely, this settings could be helpful: GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'user'@'ip' IDENTIFIED BY 'password'; If you want ...
2 votes

What is the risk of a SQL Server backup strategy that is only weekly full backups and daily transaction log backups?

My biggest concern is actually the transaction log backups. If they were to lose the database five minutes before their scheduled t-log backups they could lose almost 24 hours worth of data. More ...
2 votes
Accepted

Adding mysqldump parameters in automysql

I've added this option on /etc/default/automysqlbackup OPTIONS OPT="$OPT --lock-tables=false"

Only top scored, non community-wiki answers of a minimum length are eligible