Auto Backup to AWS S3 (DB Backup etc.)

Auto Backup to AWS S3 (DB Backup etc.)

I used to use IDC only in the past, but recently I used AWS and I kept the DB backup file in AWS S3.

This method is available in both IDC -> AWS S3, AWS EC2 -> AWS S3, and it is not only DB backup but also important server source or file.

The approximate procedure is as follows and DB backup will be used as an example.

0. Server environment

  Windows 2008 R2 Standard

  SQL Server 2008 R2 Standard SP3

  Even if the server or DB environment is different, the method is not much different.

1. Set up the backup on the DB server.

  1-1: Set DB backup to be 1 full backup per day.

  1-2: Set to be one transaction backup per hour.

  1-3: When setting up the above two backups, set the backup file to be stored in a specific folder.

2. Set up a bucket where the DB will be backed up to AWS S3.

  In this case, distinguish the folder where the full backup and transaction backup files will be stored.

3. Install the AWS CLI on the DB server.

  (AWS CLI = AWS Command Line Interface)

  3-1: Download the installation file for your server environment.

      (Http://docs.aws.amazon.com/cli/latest/userguide/installing.html)

  3-2: Set AWS CLI environment. If you type aws configure in the command window, the following input screen appears in succession.

$ aws configure
AWS Access Key ID [None]: 
AWS Secret Access Key [None]: 
Default region name [None]: 
Default output format [None]: 

  3-3: Input the value on the environment setting input screen to complete the setting. (Below is an example.)

$ aws configure
AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE
AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Default region name [None]: us-west-2
Default output format [None]: json

4. Test copy (synchronization) of DB backup files.

  Enter the following command in the command window.

  Command: aws s3 sync [DB backup file folder] [S3 bucket path]

  example:

aws s3 sync E:\DB_BackUp s3://db.backup/DbBackup

  The sync command is a command that synchronizes local files to the S3 side.

  This is convenient because you do not need to specify a file for each, and the cp command is for simple copying.

  Https://aws.amazon.com/cli/

5. Create a batch file to automate backup file copying.

  Create a batch file (bat) file and save the command you used in step 4 above.

  For example, add the command aws s3 sync E: \ DB_BackUp s3: //db.backup/DbBackup to the db_backup.bat file.

6. Add the schedule to the Task Scheduler.

  Set it in Control Panel - Administrative Tools - Task Scheduler so that the bat file created in # 5 above will run at the specified time.

7. In S3, set the deletion interval of the DB backup file.

  If the backup file is kept indefinitely, it will not be expensive, but since S3 cost is added, set the retention period and set it in S3 so that the backup file after a certain period of time is deleted.

  S3 is easy to apply because life cycle setting is not difficult.

@ Etc

If you have a DB server inside the same AWS, it is not too difficult to copy the backup file again, because there is no risk that the backup file will disappear because S3 is not 100% perfect, but it is very stable.

Next, let's take a look at AWS usage fees, AWS, Google Cloud, and MS Azure comparisons.